
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 15, 2022 04:33 PM
How can I replace decimal number with two decimal places to hourly like number.
2.42 > 2:42h
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 15, 2022 11:24 PM
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’)
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 15, 2022 11:24 PM
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’)
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 16, 2022 05:17 AM
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()))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 16, 2022 06:16 AM
I may be boring,
But do you know why does it show me numbers that are ending with “0” like this
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.
