Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 26, 2021 12:57 PM
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
May 26, 2021 01:50 PM
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.
Task Order
, where you put in an integer to show the task comes first, second, etc.Then the Tasks’ date field could be a formula like so:
DATEADD(
{Date (from Projects)},
-({Count of Tasks (from Projects)} - {Task Order} + 1),
"weeks"
)
May 26, 2021 01:59 PM
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.