Complex health status with both gray and blue options

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!

Tags:

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • jspark
    jspark ✭✭✭
    Options

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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • jspark
    jspark ✭✭✭
    Options

    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
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!