Skip to main content
Solved

Using DATETIME_DIFF with HOURS and DECIMAL rounds to whole number

  • August 22, 2016
  • 4 replies
  • 91 views
C
Forum|alt.badge.img+18

It appears that the DATETIME_DIFF() function rounds down to a whole integer when specifying hours regardless of it decimal is specified to two places.

Best answer by Amanda_Plyley

I am also searching for this solution. Is this actually a bug? Or is there a way to format the formula to return the difference between the two times in hours and minutes? Or fractions of hours expressed as a two decimal place number? I would like to be able to total the sum of hours and minutes worked in the summary bar as well.

I have searched around a lot, but haven’t found any resources specifying how to get this result with the datetime_diff formula.

Thanks in advance for the help.


Just following up with an additional idea, which seems like it will work for my needs:
DATETIME_DIFF({End Date-Time}, {Start Date-Time}, ‘minutes’) / 60

But I am still curious if there are other ways to specify the format of hours and minutes within the datetime_diff formula.

4 replies

A
Forum|alt.badge.img
  • Amanda_Plyley
  • New Participant
  • September 23, 2016

I am also searching for this solution. Is this actually a bug? Or is there a way to format the formula to return the difference between the two times in hours and minutes? Or fractions of hours expressed as a two decimal place number? I would like to be able to total the sum of hours and minutes worked in the summary bar as well.

I have searched around a lot, but haven’t found any resources specifying how to get this result with the datetime_diff formula.

Thanks in advance for the help.

A
Forum|alt.badge.img
  • Amanda_Plyley
  • New Participant
  • Answer
  • September 23, 2016

I am also searching for this solution. Is this actually a bug? Or is there a way to format the formula to return the difference between the two times in hours and minutes? Or fractions of hours expressed as a two decimal place number? I would like to be able to total the sum of hours and minutes worked in the summary bar as well.

I have searched around a lot, but haven’t found any resources specifying how to get this result with the datetime_diff formula.

Thanks in advance for the help.


Just following up with an additional idea, which seems like it will work for my needs:
DATETIME_DIFF({End Date-Time}, {Start Date-Time}, ‘minutes’) / 60

But I am still curious if there are other ways to specify the format of hours and minutes within the datetime_diff formula.

A
B
M
A
Forum|alt.badge.img+7
  • Andres_Gomez
  • Participating Frequently
  • June 19, 2018

Just following up with an additional idea, which seems like it will work for my needs:
DATETIME_DIFF({End Date-Time}, {Start Date-Time}, ‘minutes’) / 60

But I am still curious if there are other ways to specify the format of hours and minutes within the datetime_diff formula.


Thanks! It just did the trick for me.

J
Forum|alt.badge.img+2
  • Jan_Segier2
  • New Participant
  • June 18, 2020

Following up on this question:

I have 2 date fields: “start” and “stop”. Both in European timezone and 24h time format (“same time zone for all…”). 3th field is a formula field to calculate the difference in hours between start and stop (to register hours for labour workers): “DATETIME_DIFF(Einde,Start,‘HH:mm’)/3600000” (format: “decimal”; 1.00 precision).

Question: if I don’t add the “/360000” it doesn’t return eg 2.5h, but only the full hours (2h or 3h). Why? What am I missing here? Why do we have to use this workaround and how can it be 360000 for 2,5h?

Terms & ConditionsAccessibility statement