Hello, I’m trying to figure out how to automate a series of due dates for tasks. In this example, I have a project that has a deadline of 6/28/22 at 5:00pm.
The to-do list associated with this project is automatically generated through a series of automation (every project that gets added to this table will receive the same to-do list). However, each task should have its own due date based on the final deadline, listed in the “Due Date for Task” column. For example, “Save and review RFP” could be 45 days out from the final deadline of 6/28. “Kick off meeting to determine focus of proposal” could be 30 days out from the final deadline of 6/28, for example. Whenever a new project gets added to the table, I want each automated task to have its own due date based on how close it is to the project’s final deadline, following the same formula each time.
I know how to set those individual deadlines using formulas if I dedicate a new column to each individual task’s deadline and write a separate formula for it. But I need there to be one column with all the auto-generated due dates listed, one on top of the other. I know there’s not yet a way to use formulas in automations, but is there a workaround anyone has come across?
Any help is appreciated!
Hi, I’ve set something up here for you to check out.
The idea would be to use a SWITCH() to set which formula to use to get the due date
SWITCH(
{Task to Complete},
"Save and review RFP", DATEADD({Grant Deadline}, "-45", "days"),
"List all needed elements and which need signatures", DATEADD({Grant Deadline}, "-40", "days"),
"Kick off meeting to determine focus of proposal", DATEADD({Grant Deadline}, "-30", "days")
)
This is really helpful, thank you Adam. I’ve now added the SWITCH function (copied and pasted from your last post). However, I’m finding that it only lists the due date for one of the tasks, even though I’ve added the SWITCH formula for all. Do you have any ideas why this might be?
Additionally, do you have any ideas why the tasks get added out of order now?
