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

#1

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 !

#2

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

#3

I really appreciate your presence on planet earth. Thanks

#4

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 )

#5

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.

#6

@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?

#7

Are you using duration fields or date fields?

#8

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?

#9

## 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.