Status is blank when "% Completion" column is less than 100%
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"))))))
Answers
-
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!)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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..
-
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")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks so much. This helps.. My eyes are still crossing, but I get it! 😂 Gonna take this back to the team.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 395 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
