Help

Creating an End Date by adding duration to a Start Date

Topic Labels: Dates & Timezones
8953 9
cancel
Showing results for 
Search instead for 
Did you mean: 
Parker_Web
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

9 Replies 9
nea_lpatil
7 - App Architect
7 - App Architect

@Parker_Web

Following might help for use of dateadd function.

Parker_web_dateadd.JPG

Formula reference for dateadd is at

5f73751092c6afb3485d0dfe997b3809227f5002.png

Formula field reference

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

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.

Parker_web_dateadd_2.JPG

Neal

Parker_Web
5 - Automation Enthusiast
5 - Automation Enthusiast

For some reason I’m not allowed to post images.

My Status has been updated so I can post images now.

Screen Shot 2019-09-12 at 5.21.08 PM.png Screen Shot 2019-09-12 at 5.21.16 PM.png Screen Shot 2019-09-11 at 6.16.10 PM.png

nea_lpatil
7 - App Architect
7 - App Architect

@Parker_Web

It 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

Parker_Web
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

nea_lpatil
7 - App Architect
7 - App Architect

@Parker_Web

In 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

Parker_web_dateadd_3.JPG

Parker_web_dateadd_4.JPG

Parker_Web
5 - Automation Enthusiast
5 - Automation Enthusiast

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!