Incomplete work left to go

jacob sundermeyer

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?

Paul Newcome

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?

jacob sundermeyer

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

jacob sundermeyer

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.

Paul Newcome

My apologies. You are correct. Replace

 

=

with

<>

jacob sundermeyer

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

Paul Newcome

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

 

 

jacob sundermeyer

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

 

Paul Newcome

Give this a try...

 

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

jacob sundermeyer

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)))

Paul Newcome

Happy to help. Hope it works for you!