Skip to main content

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



2.42 > 2:42h

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


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


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()))


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


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.


Reply