Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Find an end date automatically using a Duration and Start date

Topic Labels: Automations
1196 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I am trying to build a Project Plan and Gantt Chart and I am entering a duration for each record + a start date. How can the end date be automatically calculated? Also, If I change the duration time for a record, the end date needs to reflect that change. Thank you

2 Replies 2

Hi @Celine_Derai - there’s a few ways to do this depending upon the result you want.

  1. DATEADD - this does a straight addition of days (or some other unit) to the chosen date:

Screenshot 2021-03-19 at 10.05.31

The formula is:

DATEADD(Start, Duration, 'days')

This might not work well in a working context as it ignores weekends, so your end date might end on a weekend of the task might cross a weekend and these days are included in your duration - not great if your team does not work at weekends.

2, WORKDAY - this is great for taking account of weekends:

Screenshot 2021-03-19 at 10.07.54

The formula here is:

WORKDAY(Start, Duration)

But this has a wrinkle you might need to think about. WORKDAY “Returns a date that is numDays working days after startDate”. Look at this example:

Screenshot 2021-03-19 at 10.09.04

The task starts on Wed 17th and is 3 days, which to me means it could be completed by the end of Friday. But Airtable calculates this a 3 days after 17th, so 18th, 19th, (off for 2 days), 22nd.

To get around this, just minus 1 from the duration in the formula (not in the duration field):

WORKDAY(Start, Duration - 1)

Screenshot 2021-03-19 at 10.11.36

More info on date formulas here

Hi @JonathanBowen,

Thank you so much for this. It worked. This is what I needed.

Celine