Calculating task dates based on project launch date

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"
)

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.