Help

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

9711 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Alec_East
5 - Automation Enthusiast
5 - Automation Enthusiast

Mabye this is an easy one for you Airtable gurus. I am currently creating a more or less complex ToDo list and want to create a field which calculates the difference between Estimate and ACTUAL use of time. Does anyone know a simple trick or formula to solve this? Many thanks in advance !

11 Replies 11
Jacob_Turley
6 - Interface Innovator
6 - Interface Innovator

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

image

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

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?