Status for At Risk tasks due in 7 days
I have 4 status categories for my project with respective RYGB balls:
- Complete (blue)
- At Risk (red)
- Requires Attention (yellow)
- On Track (green)
My current formula in the Health column automatically changes the RYGB balls when the status is changed manually:
=IF(Status2 = "On Track", "Green", IF(Status2 = "Complete", "Blue", IF(Status2 = "Requires Attention", "Yellow", IF(Status2 = "At Risk", "Red"))))
However, I would like to incorporate some automation in the Status column:
1) Status becomes "At Risk" when % Complete is less than 100% and Finish Date is within 7 days
2) Status becomes "On Track" with any other % Complete status less than 100% as long as Finish Date is outside 7 days
The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation.
I have created this formula based on my research but it is coming back as unparseable:
=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))
Any help would be greatly appreciated!
Comments
-
=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))
The bold and underlined part of your formula is causing that issue.
Try this modification:
=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 >= Today(7), [% Complete]1 < 1), "At Risk", "On Track")))
-
An Explanation:
- The "On Track" that appears after the "", is creating a non argument. I removed it because you added to the end of the third if statement.
- And statements need to be comma-separated right after the IF like this. =IF(AND(This, That), "THEN DO THIS"
That should do the trick for you.
-
Thanks so much Mike! Your formula worked correctly once I changed ">= Today(7)" to "<= Today(7)" to find tasks due in the next 7 days. My mistake for not being clear on the requirement. Appreciate your help,
-
Awesome. I am so glad I could help you out.
-
Just a heads up...
"The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation."
Once the status is manually changed, it essentially deletes the formula from the cell. Any updates made after that would have to be done manually unless the formula is re-entered back into the cell after the manual change.
-
Yes, Paul is accurate in what he is saying. It is my recommendation to automate EVERY status in the status dropdown and to lock the column because any manual changes will overwrite formulas in the cell for good.
-
You might consider adding a checkbox column for "Requires Attention" and then automating the status based on the checkbox.
-
Thanks, Mike. I guess a recommended solution would be a good idea when pointing out an issue instead of just saying "this is a problem". Haha. That's one of my "pet peeves" and I just did it. Oops!
-
@Paul - no worries. I'm glad you caught that. That was an important breakdown in the process.
-
Hi Mike,
That's a great point. I didn't realize that the formula gets deleted when the status is manually changed. I've set up a new checkbox column titled "Requires Attention" and added to my formula in the "Status" column (underlined section below is the added portion):
=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", "On Track", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))
However, I'm getting an INCOMPLETE ARGUMENT SET error. Checking the box under "Requires Attention" does nothing.
-
Try this one... There was an extra "on Track" after "At Risk".
=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))
-
That did it! Thank you!
-
Excellent! Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!