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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.