Status is blank when "% Completion" column is less than 100%

CrystalJ_Medtronic

I inherited a sheet with an existing formula to calculate the task status. It works just fine UNLESS the "% Completion" column is less than 100%. I am fully stumped how to fix it. Hoping one of the many Smart people here can help..

=IF(ISBLANK([Start Date]@row), "Not Planned", IF(AND([% Completion]@row = 0, [Start Date]@row > TODAY()), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 >= SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "On Track", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 < SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "Behind", IF(AND([% Completion]@row = 1, [Actual End Date]@row > [Target End Date]@row), "Completed Late", IF(AND([% Completion]@row = 1, [Actual End Date]@row <= [Target End Date]@row), "Completed On Time"))))))

Jeff Reisman

@CrystalJ_Medtronic

It's blank because it's not finding any conditions that match the combination of there being a Target End Date, and Actual End Date, and a % Completion less than 1. (Not mention the combo doesn't make sense - why would there be an Actual End Date if it's not complete?)

Figure out what Status you want for this circumstance and add another IF to the end of the formula (with one more end parentheses at the end!)

Ryan Sides

Hi @CrystalJ_Medtronic

That formula doesn't account for a scenario where the % Complete is less than 100%. In other words, your predecessor only wanted to see a status when it was 100%.

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/359459#Comment_359459

The combination of there being a Target End Date, and Actual End Date, and a % Completion less than 1 makes sense when you look at the parent tasks where the "Actual End Date" is driven by this formula:

=IF(AND(Parent@row = 1, [% Completion]@row > 0), MAX(CHILDREN()))

The combo shown in my earlier screenshot on the child tasks was an experiment to help me confirm that the issue wasn't just happening on the parent tasks

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/359462#Comment_359462

It appears so. Not sure why as we not only need to see task status, but the top level parent task to determine the overall program status/health. Not sure I have the skills to figure out how to fix this either.. 🤣 Thanks anyway..

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359477#Comment_359477

That's fine. You still need to add another nested IF to the formula in order to account for these criteria and indicate a status value for it.

=IF(AND([% Completion]@row < 1, ISDATE([Actual End Date]@row), ISDATE([Target End Date]@row)), "Status message here")

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/359481#Comment_359481

Okay. Thanks for the explanation.. I understand what is needed, but I'm not skilled enough to figure this out.😊 This formula has a lot going on and it's starting to feel like coding and I'm not a coder. 😂

So I'm going to see if I can adapt a formula I've used on a different sheet I also inherited (one I know how to troubleshoot) and see if I can make it work for me.

Ryan Sides

@CrystalJ_Medtronic

sounds good. Let us know if you run into any issues. Happy to help!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359507#Comment_359507

I'm not a coder either 😀 But don't worry, you got this!

Add the bold text below into your formula after "Completed On Time". Replace the text Status message here with whatever you want the Status field to show when there are dates in the Target End Date and Actual End Date fields and the % Completion is less than 100%.

=IF(ISBLANK([Start Date]@row), "Not Planned", IF(AND([% Completion]@row = 0, [Start Date]@row > TODAY()), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 >= SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "On Track", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 < SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "Behind", IF(AND([% Completion]@row = 1, [Actual End Date]@row > [Target End Date]@row), "Completed Late", IF(AND([% Completion]@row = 1, [Actual End Date]@row <= [Target End Date]@row), "Completed On Time", IF(AND([% Completion]@row < 1, ISDATE([Actual End Date]@row), ISDATE([Target End Date]@row)), "Status message here")))))))

The idea here is that, since those rows did not match any of the logical expressions in the IF formula, you add another one at the end that does match, thereby telling Smartsheet what to do when it finds them.

IFs work from left to right, and stop when they find a true logical statement (or combo of logical statements.) The basic syntax is: =IF(logical expression, value if true, value if false)

You can nest these by using another IF statement as the "value if false. For instance, I could say =IF(Status@row = "Complete", "Finished!", IF(Status@row = "Incomplete", "Stop Slacking!", "Get to Work buddy!"))

So the second IF is the "value if false" of the first logical expression.

Here is the help page for the IF function: https://help.smartsheet.com/function/if

Here is a thread on using nested IF statements: https://community.smartsheet.com/discussion/29591/multiple-if-statements

Bookmark the links in my signature, they go to the function help pages and the function error message page.

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/359535#Comment_359535

Thanks so much. This helps.. My eyes are still crossing, but I get it! 😂 Gonna take this back to the team.