I’ve tried this multiple ways by either having hours be a number field and having the formula be DATEADD({Start date}, Hours, ‘hours’) or as it is now with a duration field and saying DATEADD({Start date}, Hours, ‘seconds’)
And the hours are always off. It looks like its taking the start time hour and adding it to start time rather than the actual duration hours field. Any thoughts on how to fix this?
Yes I’ve selected the same timezone box.
+2Following might help for use of dateadd function.
Formula reference for dateadd is at
For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...
Hope it helps.
Neal
+17Can you post a screenshot of the result you’re seeing? I’ve found Airtable has a tendency to treat time values as UTC, which screws up the output of formulas, particularly when one of the date fields in question doesn’t have a time field.
For example, here’s an shortened version of a formula I currently use, which calculates the days between an embargo and when an article goes live. As you can see, I have to use the SET_TIMEZONE function even though I’m calculating a difference in days, not hours.
DATETIME_DIFF(
DATETIME_FORMAT(
SET_TIMEZONE( {Embargo Date} , 'America/Toronto' ),
'YYYY-MM-DD' ),
{Scheduled Date},
'days'
)
Can you post a screenshot of the result you’re seeing? I’ve found Airtable has a tendency to treat time values as UTC, which screws up the output of formulas, particularly when one of the date fields in question doesn’t have a time field.
For example, here’s an shortened version of a formula I currently use, which calculates the days between an embargo and when an article goes live. As you can see, I have to use the SET_TIMEZONE function even though I’m calculating a difference in days, not hours.
DATETIME_DIFF(
DATETIME_FORMAT(
SET_TIMEZONE( {Embargo Date} , 'America/Toronto' ),
'YYYY-MM-DD' ),
{Scheduled Date},
'days'
)
Result of formula I showed earlier
Actually I used the same formula so I was able to show result and formula in the same screenshot.
Neal
For some reason I’m not allowed to post images.
My Status has been updated so I can post images now.
+2It looks like your Hours field is of type duration.
What I understand from formula reference that it should be of type number.
Can you change type of field=Hours from Duration to Number(Integer) and check the results?
Neal
As I wrote above, I had the “hours” as a number field first and my format was DATEADD({Start date}, Hours, ‘hours’). The duration field should work according to other blog posts as long as I changed the formula to DATEADD({Start date}, Hours, ‘seconds’) because the output is in seconds.
No matter how the field or the formula are formatted, the result is always the same.
+2In my case it works like in the following screenshots.
Field type=number(Integer) is the main difference.
Are you expecting results as in the following ?
Neal
I figured it out! While I had the formula set to the same time zone for all collaborators, the start date was not… So it was adding four hours to my end date. Seems like a weird bug but glad its working now!
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.