Complex health status with both gray and blue options

jspark

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!

Ryan Sides

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"))))), "")

jspark

Unfortunately that gives me a "column formula isn't quite right" error.

Ryan Sides

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"))))), "")

jspark

Thanks @Ryan Sides ! It mostly works (just had to change the name of the date columns), with a small issue:

1. 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!

Ryan Sides

@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