Tracing Predecessors of Late Tasks

Misty Antonacci

I am trying to create a formula to be able to show what predecessor task in the process is holding up later successors. The plan is used for onboarding physicians in a health system, so multiple areas with overlapping tasks [HR, the hiring department, the team that assigns clinic space, etc.].

The simple version of this is, say we have 3 tasks. Task 3 cannot be completed until Task 2 is done, and Task 2 cannot be completed until Task 1 is done. If Task 1 is late and causing the other two to be late, we want to be able to show the owners of Task 3 that Task 1 is really their constraining factor, even though Task 2 is their direct predecessor. Simple with just a few predecessors, but the full plan is 100+ tasks with multiple overlapping flows and dependencies.

Leveraging this community post, I was able to create a "Predecessor Complete, Ready to Start" checkbox to be able to see if the prior tasks are done. I was able to use the helper columns there to see what is causing a task to be late one level up, but I'm struggling to get beyond that.

The screenshot below is where I sit now - as an example task 10 is fully late, all predecessors are complete so it can be completed. I want to show that both task 11 and task 12 are "constrained" because of task 10, even though task 12's direct predecessor is task 11.

I've been able to pull columns for both what is causing something to be late, and what is causing it to be constrained [screenshots of the formulas below]. Essentially my aim is to add a third column to say is there is something in the "late predecessor" column, return that, otherwise keep checking the "constraining predecessors" until one of them has something late. I've had some success with some nested if / index / match formulas but I can't quite get it. There's also more complication when there are multiple predecessors, but even something that works in 80% of cases is fine for this.

Not sure if that is the best approach, so open to more ideas, just looking for input if anyone has attempted something similar or has any help on those nested formulas?

Laurie Olson

I am not sure on what has been built so far. I would take a different approach.

My suggestion would be to include a flag on a row by row basis to indicate each task that is behind. You can then filter the late tasks. I would also look into using the Critical Path indicator.