Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Decimal number with two decimal places (2.45) to hours (2:45h)

Topic Labels: Formulas
Solved
Jump to Solution
1445 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael1
6 - Interface Innovator
6 - Interface Innovator

How can I replace decimal number with two decimal places to hourly like number.

image

2.42 > 2:42h

1 Solution

Accepted Solutions
Mohamed_Swella1
13 - Mars
13 - Mars

Hi @Michael1 ,

I would do the following:

The lookup field would be a Rollup and set to get the AVERAGE(values)

Then in the formula field I would do something like this

DATETIME_FORMAT(DATETIME_PARSE({Numbers},‘hh:mm’),‘hh:mm’)
Screen Shot 2022-03-16 at 9.22.40 AM

Or if you want to keep it as a LookUp field you can put the formula like that
DATETIME_FORMAT(DATETIME_PARSE(AVERAGE({Numbers}),‘hh:mm’),‘hh:mm’)

Hope this helps

See Solution in Thread

3 Replies 3
Mohamed_Swella1
13 - Mars
13 - Mars

Hi @Michael1 ,

I would do the following:

The lookup field would be a Rollup and set to get the AVERAGE(values)

Then in the formula field I would do something like this

DATETIME_FORMAT(DATETIME_PARSE({Numbers},‘hh:mm’),‘hh:mm’)
Screen Shot 2022-03-16 at 9.22.40 AM

Or if you want to keep it as a LookUp field you can put the formula like that
DATETIME_FORMAT(DATETIME_PARSE(AVERAGE({Numbers}),‘hh:mm’),‘hh:mm’)

Hope this helps

Thanks that helped!
I just had problems with NaN fields and ERROR after that, but I just combined ISERROR with your formula.

IF(ISERROR(AVERAGE({Numbers}))=0, DATETIME_FORMAT(DATETIME_PARSE({Numbers},‘hh:mm’),‘hh:mm’,BLANK()))

I may be boring,
But do you know why does it show me numbers that are ending with “0” like this
image

Formulas I used:
Lookup Numbers > AVERAGE(values)
test 0316 > IF(ISERROR(AVERAGE({Lookup Numbers}))=0, DATETIME_FORMAT(DATETIME_PARSE({Lookup Numbers},‘hh:mm’),‘hh:mm’,BLANK()))

Had to use ISERROR because of error and NaN.