Complex health status with both gray and blue options

Hi,
I am trying to generate a health status formula for my sheet with the following conditions.
Ideally I would like to have green, red, yellow, grey, and blue health options but I am not sure if that is possible. If so, my conditions are:
Blue: Status column is "Completed" (regardless of start or finish dates)
Green: Status column is "In Progress" and the Finish date is 7 days out or further, or Status column is "Preparing" and Start date is 14 days out or further.
Yellow: Status column is not "Completed", "Paused", "Deprioritized", or "Cancelled", and the Finish is within the next 7 days.
Red: Status column is not "Completed", "Paused", "Deprioritized", or "Cancelled", and the Finish is within the next 2 days or is in the past.
Gray: Status column is "Paused" or "Cancelled" or "Deprioritized"
Blank: Health column is blank if the Status column and Finish columns are both empty.
If only gray or blue is possible, then my conditions are:
Green: Status column is "Completed", or Status column is "In Progress" and the Finish date is 7 days out or further, or Status column is "Preparing" and Start date is 14 days out or further.
Yellow: Status column is not "Completed", "Paused", "Deprioritized", or "Cancelled", and the Finish is within the next 7 days.
Red: Status column is not "Completed", "Paused", "Deprioritized", or "Cancelled", and the Finish is within the next 2 days or is in the past.
Gray: Status column is "Paused" or "Cancelled" or "Deprioritized"
Blank: Health column is blank if the Status column and Finish columns are both empty.
I tried working off of https://community.smartsheet.com/discussion/85935/health-formula but ran into some syntax errors when adding conditions.
Thank you for any help you can provide!
Answers
-
Hi @jspark
Give this one a try...
=IFERROR(IF(OR(Status@row = "Completed", AND(Status@row = "In Progress", [Finish Date]@row > TODAY(7)), AND(Status@row = "Preparing", [Finish Date]@row >= TODAY(14))), "Green", IF(AND(Status@row = "", [Finish Date]@row = ""), "", IF(OR(Status@row = "Paused", Status@row = "Cancelled", Status@row = "Deprioritized"), "Gray", IF(AND(OR(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled"), [Finish Date]@row < TODAY(2)), "Red", IF(AND(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled", [Finish Date]@row >= TODAY(), [Finish Date]@row < TODAY(7)), "Yellow"))))), "")
-
Unfortunately that gives me a "column formula isn't quite right" error.
-
Hmmm. Working over here. Try copy/paste this...
=IFERROR(IF(OR(Status@row = "Completed", AND(Status@row = "In Progress", [Finish Date]@row > TODAY(7)), AND(Status@row = "Preparing", [Finish Date]@row >= TODAY(14))), "Green", IF(AND(Status@row = "", [Finish Date]@row = ""), "", IF(OR(Status@row = "Paused", Status@row = "Cancelled", Status@row = "Deprioritized"), "Gray", IF(AND(OR(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled"), [Finish Date]@row < TODAY(2)), "Red", IF(AND(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled", [Finish Date]@row >= TODAY(), [Finish Date]@row < TODAY(7)), "Yellow"))))), "")
-
Thanks @Ryan Sides ! It mostly works (just had to change the name of the date columns), with a small issue:
- The formula returns a blank on health for some rows with "Preparing" status, how can I rewrite the formula such that it shows as:
- green if a row is "Preparing" + the start date is more than 14 days in the future
- yellow if a row is "Preparing" + start date is 14 or fewer days in the future
Thank you so much for the help!
- The formula returns a blank on health for some rows with "Preparing" status, how can I rewrite the formula such that it shows as:
-
@jspark You can use this one...
=IFERROR(IF(OR(Status@row = "Completed", AND(Status@row = "In Progress", [Finish Date]@row > TODAY(7)), AND(Status@row = "Preparing", [Finish Date]@row >= TODAY(14)), AND(Status@row = "Preparing", [Start Date]@row > TODAY(14))), "Green", IF(AND(Status@row = "", [Finish Date]@row = ""), "", IF(OR(Status@row = "Paused", Status@row = "Cancelled", Status@row = "Deprioritized"), "Gray", IF(AND(OR(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled"), [Finish Date]@row < TODAY(2)), "Red", IF(AND(Status@row <> "Completed", Status@row <> "Paused", Status@row <> "Deprioritized", Status@row <> "Cancelled", [Finish Date]@row >= TODAY(), [Finish Date]@row < TODAY(7)), "Yellow", IF(AND(Status@row = "Preparing", [Start Date]@row <= TODAY(14)), "Yellow")))))), "")
Just remember to change the column names again
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!