Skip to main content

Linking succesor start dates to predecessor record completion dates

  • February 18, 2020
  • 1 reply
  • 36 views
S

Hi - I’m trying to link a chronological set of tasks based on the completion date of a proceeding task. I’ve figured out how to project the due date by taking the start date plus Duration (in days) to get a projected due date, however, i’m stuck how to link this to the next record.

For example Task 1 is completed on 1/1/20

Task 2 should have a start date of 1/1/20 plus some duration (say 3 days) and have a projected due date of 1/4/20

Task 3 will then have start date of 1/4/20 and so on…

any help would be appreciated!

1 reply

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • February 22, 2020

It sounds like you want something like

{start date} = lookup of {end date} in linked previous record 
{end date} = DATEADD({start date}, {duration in days}, 'days')

However, that will not work because it will create a circular reference in the formulas. Because the {start date} and {end date} are both calculated off of each other, Airtable cannot figure out where to begin the calculations!

Even if you have records linked so that it should work in theory (like in a spreadsheet), Airtable will still say that you have a circular reference. This is because (1) a user could still potentially create a circular reference by linking records in a loop, and (2) editing the value for one record could potentially change the values of all the records in the table, which would be a large computing load.

My workaround has been to have a manually entered {planned start date} and manually copy the lookup {expected start date} into it. If the {duration} is the same for all rows, it can be done in a single copy/paste (offset by one record); otherwise, it is a lot of manual copy/paste for each affected record.

I also create an additional field that checks if the planned and expected start dates are the same or not.

{previous task} = 
  link to a record in the same table

{planned start date} = 
  regular date field, manually copied from {expected start date}
 
{expected start date} = 
  lookup of {projected end date} in linked {previous task}

{projected end date} = 
  DATEADD({planned start date}, {duration in days}, 'days') 

{date-check} = 
   IF(IS_SAME({planned start date}, {expected start date}, 'd'), 
   "", 
   "Warning: planned start date does not match expected start date"
  )
Terms & ConditionsAccessibility statement