- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:10 AM
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 !
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:25 AM
DATETIME_DIFF({Estimated Date},{Actual Date},‘hours’)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:25 AM
I really appreciate your presence on planet earth. Thanks :slightly_smiling_face:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:27 AM
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: )
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:31 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:32 AM
@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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:35 AM
Are you using duration fields or date fields?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:37 AM
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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 29, 2018 11:54 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 17, 2019 03:07 PM
how would this question be answered if someone was using the date field?