Autofill Task Dates Based on Project Due Date

[SIairtable]

Hi all!

I am looking to automatically calculate a sub tasks draft date based on identifying the entire project’s DUE DATE. Let’s say that x project is due on 6/06/20 - I want to then automatically populate a calendar based on that date:
ie.
Task 1 is (-10) work days from 6/6/20
Task 2 is (-5) work days from 6/6/20
Task 3 is (- 3) work days from 6/6/20
…and so on

What would be the best way to do this?
Thank you all!

[Mohamed_Swella1]

Hi @SIairtable,

You need to use the DateTime_Diff formula to calculate these dates. It would be something like

DateTime_Diff({Project Due Date}, 10, ‘days’)

BR,
Mo

[Kamille_Parks]

Assuming you have a [Projects] table and a [Tasks] table where every task is linked to a project, and the [Projects] table has a date field called {Due Date}:

Your [Tasks] table should have a lookup field pulling in {Due Date}, and you may have a number field where you enter how many days in advance that task should be completed called {Days in Advance}. Then you could have a formula that looks like this: WORKDAY({Project Due Date}, {Days in Advance}).

[Kamille_Parks]

OP wants to subtract time in work days, meaning WORKDAY() is the more appropriate function than DATETIME_DIFF(). Using DATETIME_DIFF() will include weekends whereas WORKDAY() will not.

[Mohamed_Swella1]

Yes you are correct, I didnt notice the Workday part.

[Justin_Barrett]

While WORKDAY() is definitely the choice to exclude weekends, there’s some confusion in this discussion, with DATETIME_DIFF() getting mixed up with DATEADD(). I believe that @Mohamed_Swellam meant to list DATEADD() originally, which is the correct option for calculating new dates based on a start date and offset. DATETIME_DIFF() is used for calculating the span of time between two dates.

[SIairtable]

Thank you all! Very helpful -

Still having some trouble since the workdays are actually starting on Sundays - so the week is Sun - Thursday

Has anyone else had that problem?

[Justin_Barrett]

My gut says that it’s a timezone issue. Under the hood, it’s probably working correctly and operating with Monday-Friday days, but the days and times coming back will likely need to be shifted to show up properly, and the specific amount of shift will depend on your timezone. You can probably figure out how far off everything is and use DATEADD() to make the adjustment.