Calculate Start Date for task dependencies

The If statement will only let me search in the same row, but I would like to be able to search the whole column for a specific value. if anyone knows a workaround for a WHERE statement that would be ideal.

Hi Michael, as you’ve discovered, formulas can only pull data from the same row they’re in.

If you’re open to changing up your workflow / base set up a bit, I would recommend you create a linked field in the table to link tasks together like so:

With this method, any changes to the Due Date value of Task 1 will automatically show up in the Start Date field of Task 2

You can find the set up here

If you don’t want to use linked fields like in the example above, the only way to accomplish what you’re trying to do would be with a script I think, but that would get complicated very quickly. Let me know if that’s really something you want to do, but I really do not recommend it unless you’re already familiar with JavaScript

Thank you for the input! This is very helpful. I will try it out and see how it works before attempting to create a script.

Alright, I have run into another problem when adding a layer to my formula. In order for me to add a due date the the newly added start date I want to reference a duration field so that the due date is automatically calculated.

My problem now is that I am running into a circular reference which is preventing me from being able to automatically calculate the end date for tasks.

Apologies, I’m finding this hard to visualize. Would it be possible to provide a screenshot of the fields please?

Here’s my attempt at trying to mimick your set up; not sure if it helps!

Yup. Circular reference errors are a major difference between spreadsheets and a database system like Airtable.

What you want is difficult to impossible in Airtable without scripting or massive numbers of automation runs.

Does anyone have a suggestion on a better way to track tasks with different due dates and dependencies for a project? even if it might be outside of Airtable I am just curious to gather thoughts about this.

Hey,

I was trying to setup tasks with duration that can correctly setup start/end dates automatically for a planning project, but it seems impossible with linked fields (it says that I'm creating a circular reference if my trying to link start date with predecessors dates, while I'm not, but yeah, the systems seems to think so)

I must say that it is very unfortunate that there is not such a built-in support. It makes the Gantt barely practical beyond an hello world Gantt with hardcoded start/end dates, while the "beauty" of Gantt is to actually adapt automatically when adjusting the duration of a task.

Hope that Airtable will get this feature because it is quite nice otherwise to use!

Hello alexandre_mutel . I do believe that this is possible with a bit of a workaround:

In short, the key is to get around the circular reference by taking a copy of the desired Start Date from the predecessor record and pasting it as raw, "unlinked" data in another column. This can be accomplished via automation.

The table below is a screenshot of how I accomplished this.

1) In the "Start Date" column I have a formula that will pull the date from the "State Date (Override)" column only if the "Start Date Copy" column is blank.

2) So how does the "Start Date Copy" column get populated? Well, I have an automation that is triggered when the "Start Date-From Nearest Blocker" column becomes populated. Once this occurs, that same date is copied into the "Start Date Copy" column, thus eliminating the circular reference.

3) But what is the "Start Date-From Nearest Blocker" column and how did it get populated? Well. First I created the "Nearest Blocker" field as a linked record, pulling in all the records from this same, exact table. Then I created the "Start date-From Nearest Blocker" field as a lookup, pulling the value of the Start Date from the record noted in the "Nearest Blocker" field. 

4) And wiz, bang, it all seems to work.

I pray this is helpful my friend.

Hello Michael_Burnett

I do believe that this is possible with a bit of a workaround:

In short, the key is to get around the circular reference by taking a copy of the desired Start Date from the predecessor record and pasting it as raw, "unlinked" data in another column. This can be accomplished via automation.

The table below is a screenshot of how I accomplished this.

1) In the "Start Date" column I have a formula that will pull the date from the "State Date (Override)" column only if the "Start Date Copy" column is blank.

2) So how does the "Start Date Copy" column get populated? Well, I have an automation that is triggered when the "Start Date-From Nearest Blocker" column becomes populated. Once this occurs, that same date is copied into the "Start Date Copy" column, thus eliminating the circular reference.

3) But what is the "Start Date-From Nearest Blocker" column and how did it get populated? Well. First I created the "Nearest Blocker" field as a linked record, pulling in all the records from this same, exact table. Then I created the "Start date-From Nearest Blocker" field as a lookup, pulling the value of the Start Date from the record noted in the "Nearest Blocker" field. 

4) And wiz, bang, it all seems to work.

I pray this is helpful my friend.

Hi @wellwisher 

I am struggling with a similar problem and I'm not sure if you solution would also work in my case:

I am building a master Project Management Base for my colleagues. The goal is, for each new project copy it and just change the start date and all the other dates for each step would change accordingly (like cascading down). Also in case a certain task within a project took longer (eg. 5 instead of 2 days) the remaining due dates should move back as well.

I could only create a list, where I use the end date of one task as start date of the next date. but I still need to enter the dates manually for it to work. Thanks a lot in advance 🙂 

Hello Helenelene. After looking at the problem you're trying to solve, it is fundamentally the same as the problem that I was trying to solve as far as I can see. The fundamental air table issue in a case like this is that you get a circular reference when you are using data from a column that you are looking up from the same table. In cases like this the data must be copied to a column that has no dependency on the looked up column. So applying automation in the manner that I applied it will likely solve the issue. Does that help?