Incomplete work left to go
Is there a way to calculate work days "left to go" where the task is not marked complete while ignoring simultaneous tasks (tasks that have the same predecessor)?
It seems like networkdays should get part of the way there but it seems like i need an "if" statement to look at complete.
Is there another way to get there that I'm overlooking?
Comments

If you create a helper column [Remaining Durations], you can enter the following formula and dragfill down the column:
=IF([Status Column Name]@row = "Complete", Duration@row / COUNTIFS(Predecessors:Predecessors, Predecessors@row))
You can then use a basic =SUM([Remaining Durations]:[Remaining Durations]) to add everything up.
This will only look at durations for tasks that are not complete. It will look at how many times the same predecessor is found and divide the duration by that. Basically if two tasks each last for 1 day but have the same predecessors and are incomplete, it will show as 0.5 for each task. You then sum those totals up, and there you go.
Will this work for you?

That seems like it will work very nicely! Thanks, Paul!

Although on second glance, it seems that the formula is backwards and is counting things marked as complete instead of things NOT marked complete. A minor tweak to make it work.

My apologies. You are correct. Replace
=
with
<>

Interestingly, when a task has no predecessors, it gives a 0 day result.

Hmm. I hadn't thought of that happening. Would you want to treat them as having the same predecessor or as having different predecessors?

I think we would count them as different predecessors so that it would count each of their row durations.

Give this a try...
=IF([Status Column Name]@row <> "Complete", IF(NOT(ISBLANK(Predecessors@row)), Duration@row / COUNTIFS(Predecessors:Predecessors, Predecessors@row), Duration@row), "")

That is a slimmer version of what I landed on, below. I'll give it a try. Thanks for all your help!
=IF(AND(Status@row <> "Complete", Predecessors@row = ""), Duration@row, IF(AND(Status@row <> "Complete", Predecessors@row <> ""), Duration@row / COUNTIFS(Predecessors:Predecessors, Predecessors@row)))

Happy to help. Hope it works for you!
Help Article Resources
Categories
Check out the Formula Handbook template!