Incomplete work left to go

jacob sundermeyerjacob sundermeyer ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
12/04/18 Edited 12/09/19

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?

Screen Shot 2018-12-04 at 10.11.24 AM.png

Comments

  • Paul NewcomePaul 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", [email protected] / COUNTIFS(Predecessors:Predecessors, [email protected]))

     

    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?

    thinkspi.com

  • jacob sundermeyerjacob sundermeyer ✭✭✭✭✭

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

  • jacob sundermeyerjacob 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 NewcomePaul Newcome ✭✭✭✭✭

    My apologies. You are correct. Replace

     

    =

    with

    <>

    thinkspi.com

  • jacob sundermeyerjacob sundermeyer ✭✭✭✭✭

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

  • Paul NewcomePaul 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?

     

     

    thinkspi.com

  • jacob sundermeyerjacob sundermeyer ✭✭✭✭✭

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

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Give this a try...

     

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

    thinkspi.com

  • jacob sundermeyerjacob 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([email protected] <> "Complete", [email protected] = ""), [email protected], IF(AND([email protected] <> "Complete", [email protected] <> ""), [email protected] / COUNTIFS(Predecessors:Predecessors, [email protected])))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. Hope it works for you! yes

    thinkspi.com

Sign In or Register to comment.