Calculate start and end based on predecessors and duration


#1

It would be very helpful if I could link tasks as dependencies (which is already possible) and have my dependent task start dates calculate off its predecessor’s end date. Just like in MS Project, all users would need to input would be the predecessor and duration, then the start and end dates would be automatically calculated.

For now, I’m having to use MS Project or Excel to calculate all the start and end dates based on linkage and duration, then copy/paste that information into Airtable for every single task. When a task date changes, it could impact dozens of other tasks’ timelines. Using only Airtable, I would have to manually check every single task (my projects have upwards of 100 tasks) to decide if it is impacted by the change, and then calculate and update the dates for every impacted task.

This is very time consuming so when a client asks “what is the total impact if I’m 4 days late with this task?” I can’t just make the update and see the change. I have to use a separate tool to calculate that change and get back to them with the response. Needing to rely on a separate tool also means I have to maintain all tasks in both tools. When anything changes, I need to record it both in Airtable and in MS Project.


#2

I’m pretty sure you could do this if you are already linking Tasks to Projects as children of the Project.

Assuming your Project has a “Due Date” field (a “date” field type), and assuming you link each task for the Project to that Project (so that each Project can have many Tasks, but each Task belongs to only one Project):

  • Create a number field in your Tasks table called “Days Before Project Close”, and you’d enter an integer in there to represent how many days before the Project’s due date this Task is due on
  • Create a lookup field in your Tasks table called “Project Due Date” that looks up the linked Project -> Due Date field
  • Create a formula field for the “Due Date” in your Tasks table with this formula:
DATEADD(
   {Project Due Date},
   {Days Before Project Close},
   'days'
)

Then, when you change the due date of a Project, all of it’s attached tasks will also have their due dates change as relative to the Project Due Date.

You could even take it a step further to allow an override of a Task’s relative Due Date with a manual Due Date:

  • Create a date field in your Tasks table called “Manual Due Date”
  • Amend the Task “Due Date” formula above to this:
IF(
   {Manual Due Date},
   {Manual Due Date},
   DATEADD(
      {Project Due Date},
      {Days Before Project Close},
      'days'
   )

Then, it will show the Manual Due Date if you set one, and if you don’t set one, it will use the Relative Due date based on the Project Due Date.


#3

Hey Jeremy,

Thanks for the advice. This isn’t quite what I’m looking to do, though. I have tasks linked to a single project, like you outlined. What I need to do is say “Task 1 needs to be complete before Task 2 can start. Task 1 ends on Monday, Task 2 takes 3 days.” I want the software to tell me that Task 2 will be complete on Friday.

I know this is a little different, but my projects don’t really have due dates. I need to calculate when the project will be complete (as well as certain milestones) based on the duration of the tasks.