Calculating task dates based on project launch date

[Melanie_Frome]

Hi Everyone, hoping for some help…I’ve browsed several topics/posts and have not found the solution I’m looking for yet…

Say the project event date is July 25th…and it has 16 tasks that are each dated 1 week prior. Example TASK1 = WK01 which is 16 weeks PRIOR to July 25th. TASK2 = WK02 which is 15 weeks PRIOR to July 25th …and so on…I tried a ‘switch’ formula which was promising but still didn’t give me the effect I wanted.

Thank you in advance,
Mel

[Kamille_Parks]

If you want to do this without Automations you’re going to need some fields to help calculate the dates.

If Project is a record, and each Task is another record linked to the Project, then you could add the following fields.

• Project records:
  • A count field to show the total number of tasks assigned
  • A date field
• Task records:
  • A number field for Task Order, where you put in an integer to show the task comes first, second, etc.
  • A lookup field for the project’s total number of tasks
  • A lookup field for the project’s date field

Then the Tasks’ date field could be a formula like so:

DATEADD(
   {Date (from Projects)}, 
   -({Count of Tasks (from Projects)} - {Task Order} + 1),
   "weeks"
)

[kuovonne]

To calculate a start date, you need to add a negative number.

DATEADD({End Date}, -1 * {number of weeks}, 'weeks')

Notice that each record needs to be able to calculate its own number of weeks. Airtable does not have automatic support for this. There are several different workarounds.

One method is to have a number field that is either manually entered or entered by an automation or entered by a script.

Another method is to calculate the order of the records in the project’s linked record field using rollups. The system of rollups and formulas would depend slightly from base to base.