Calculate start and end based on predecessors and duration

[Lauren_Zinsmeis]

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.

[Jeremy_Oglesby]

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.

[Lauren_Zinsmeis]

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.

[Duncan_Fischer]

Hi Jeremy,

I am trying something similar,

DATEADD(
{appointment start time},
{appointment duration}, ---->> this field however is via a LOOKUP of another table and it wont work
‘mins’
)

[Follansbeast]

Hi, I am also trying to do something very similar.

I currently have the following:

Task Name
Start Date
Est Start Date - IF({Start Date}!=0,{Start Date},{Dependents Completion Date})
Duration
Est Completion Date - Est Start Date + Duration
Dependencies - Link to Tasks
Dependents Completion Date - Rollup/Dependencies/Est Completion Date/Max()

From this, I am getting a circular dependency error. Yes, circular dependencies can occur with this, but only if you have selected your dependencies incorrectly.

The intent is to have a fluctuating completion date that gets more firmed up as Start Date and Completion Date are filled in. Completion Date has not been implemented, but it would be easy enough. I see this circular dependency issue as being the primary inhibition to performing this type of task management.

Is there a way to remedy this?

[W_Vann_Hall]

Follansbeast:

I see this circular dependency issue as being the primary inhibition to performing this type of task management.

Is there a way to remedy this?

Unfortunately, Airtable currently plays it very conservatively when it comes to circular dependencies. The only workaround I’ve managed to find involves a manual hack: Rather than have your formula output feed directly into its input, define a separate input field and copy-and-paste the resulting value into the input field. It’s not very pretty, admittedly, but it works. With luck, a later release of Airtable might support tuneable dependency sensitivities…

[Jack_Finnerty]

Being able to add dependencies based on task start and end dates to create project timelines is pretty fundamental to a project management tool.

In fact the Timeline Block seems to have very limited use without this functionality.

I like everything else Airtable does, especially all the templates it has, but I want to standardize on a single tool to organize all of my company operations and this circular dependency limitation means Airtable is currently a no-go.

[Liam_Shannon]

Absolutely - this would be a commonly used feature for any project management timeline.

[W_Vann_Hall]

Take a look at this from Airtable Universe. In combination with the recently released Gantt Block, it might give you what you need.

[Liam_Shannon]

It’s a dirty way of getting to what we want to do I guess, but it’s a pretty full on work around and turns a relatively simple task in concept into a pretty daunting display. These relatively small hurdles are what makes the sell really hard to non-coders, without whom this software doesn’t address the key problem we’re trying to solve.

[W_Vann_Hall]

tl;dr version: It’s a floor wax — and a dessert topping! But it’s up to you to decide when to squirt it on your linoleum or spray it on your strawberries.

---

Well, remember Airtable is not a project-management system: It’s a relational database. As such it needs to be able to support a number of different data structures, use cases, and workflows — and any assumptions it makes about how one’s data are arranged or what one’s process may be limits its usefulness for other structures or processes. If all you need is project management software — and you can find project management software that thinks the way you do — then your best bet is probably to use project management software. But if you need, say, an integrated CRM, invoicing, shipping, and calendaring tool that also supports an employee directory and a customer mailing list — oh, and that also does project management — you’re probably going to have to build it yourself.

Airtable lets you build such a beast yourself — but, more importantly, Airtable Templates, Airtable Universe, and the example bases posted here and elsewhere by hundreds of users give you literally hundreds of ready-to-use applications that address a wide range of business and personal needs. Like any software, they probably won’t be a 100% fit for your precise use case; maybe they’re only a 90%, 80%, 70% match — maybe only a 40% or 50% match. But — and here’s the big difference, and what the sale to non-coders should be — they give you a system that often will have you up and running in minutes… and that can progressively be made to meet more and more of your needs. Try that with a pre-packaged app.

In my 2 years (well, 26-ish months) of working with the product, I’ve published, geez, 150? 180? example bases. (Plus a number of non-public bases built for users with proprietary needs or for my consulting clients, of course.) Some have provided a solution to a niche problem; others, like the scheduling framework, are essentially ready-to-run applications. And every one of them is, by the nature of the product, completely open for modification and improvement. Many are heavily documented: there’s a 22-page user’s guide embedded in the framework; a 32-page guide and a 20+ minute instruction video accompanies my data deduplication routines; and my wardrobe manager contains 5 guides totaling some 90 pages. And there are dozens of other users here who made similar contributions.

Is Airtable perfect? Of course not. Is it continually improving? In my experience, yes. Most importantly, though, it has a top-notch development and support team and a devoted and generous user base behind it. Can it do what you need it to do? Quite possibly — but it depends on what you need done.