Skip to main content
Solved

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

  • March 15, 2022
  • 3 replies
  • 32 views
M
Forum|alt.badge.img+2

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

2.42 > 2:42h

Best answer by Mohamed_Swella1

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

3 replies

Mohamed_Swella1
Forum|alt.badge.img+17

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

M
M
Forum|alt.badge.img+2
  • Michael1
  • Author
  • Known Participant
  • March 16, 2022

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

M
Forum|alt.badge.img+2
  • Michael1
  • Author
  • Known Participant
  • March 16, 2022

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.

Terms & ConditionsAccessibility statement