Calculate the difference in hours based on two time fields [SOLVED]

DATETIME_DIFF({Estimated Date},{Actual Date},‘hours’)

I really appreciate your presence on planet earth. Thanks :slightly_smiling_face:

This is pretty simple, but you have to just keep in mind that a “Duration” field holds its value in seconds.

So if you have a duration field, and you enter 1:00 in it to represent 1 hour, and then you create a formula field that just returns the value of that duration field, you’ll find that the formula field returns 3600 - because there are 3600 seconds in an hour.

The implication is that if you have two duration fields (Expected and Actual), and you subtract one from the other in a formula field, you need to divide the result by 3600 if you want it to return in hours; or by 60 if you want it to return in minutes.

When Expected = 1:00
And Actual = 1:02
Difference =

({Expected} - {Actual})/60 & " minutes"

will return -2 minutes

(Seeing the reply that was just sent in above mine, it becomes evident that you weren’t talking about duration fields… maybe someone will still find this useful anyhow :grinning_face_with_smiling_eyes: )

({Expected} - {Actual})/60 & " minutes"

Thank you very much. As I inserted the formula it seems that the time difference is not being viewed or summed up. Therefore, must I remove the minutes? What can i do that it detects it as a value? Many thanks in advance.

@Jacob_Turley As I inserted your formula, there was a failure. Is the “hours” part being inserted as well or is this value also changed/customized according to the table?

Are you using duration fields or date fields?

I am using a duration fields. According to that it sums them up, what the formula field unfortunately isn’t. Is there a solution to sum up these values when collapsed in group?

The solution in calculating the difference and summing it up is the formula:

({Expected} - {Actual})/60
After adding the formula into the “Formula field” you simply have to group (by whatever value you like; Can be per month or day, or by task type) and then collapse the whole group.
After collapsing the group, click on “sum” and choose “sum” from the dropdown.

how would this question be answered if someone was using the date field?

“how would this question be answered if someone was using the date field?”

See the first reply to the post, the one from @Jacob_Turley.

Same.

Don’t copy/paste because it’s not good single quotes (’ instead of ’)
You can just copy/paste DATETIME_DIFF and follow online help too.