R/Y/G/B Health Formula
Hi, trying to get my Health formula to work for the status of tasks. Got the 'Blue' for Complete part of the formula correct, however when trying to build out the rest of the formula I keep getting syntax errors. The Health definitions the formula needs to perform are as follows. Any examples greatly appreciated.
GREEN:
Status is In Progress AND
% Complete is 1% - 99% AND
Completion Date (Planned) is > 60 days in the future
YELLOW:
Status is In Progress AND
% Complete is 1% - 99% AND
Completion Date (Planned) is < 60 days in the future
RED:
Status is Not Started OR
Status is In Progress AND
% Complete is 1% - 99% AND
Completion Date (Planned) is < 30 days in the future OR
Completion Date is in the past.
Answers
-
Hi Lynne,
Try this:
=if(Status@row="Complete","Blue",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row>TODAY(60)),"Green",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row<=TODAY(60)),"Yellow",if(Status@row="Not Started","Red",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row<TODAY(30)),"Error"))))
I threw the "Error" part in just as a check. Let me know if it works!
Best,
Heather
-
Hi Heather,
Thanks for your response. I tried and got syntax errors. However, thankfully got the formula worked out in my ProDesk session with Smartsheet and sharing the formula below:
=IF(AND(Status@row = "Complete", [% Complete]@row = 1), "Blue", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row < TODAY(30)), "Red", IF(AND(Status@row = "In Progress", [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row < TODAY(60)), "Yellow", IF(AND(Status@row = "In Progress", [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row >= TODAY(60)), "Green", IF(Status@row = "Not Started", "Red")))))
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!