Looking for a formula that uses status and finish to calculate health.

Hello Smartsheet experts,

I am trying to write a formula that accomplishes the following in a health column with the Red, Yellow, Green, and Blue options...

Status "Complete" = Green

Status "On Hold" = Blue

Finish "Blank"(or "") = "Blank"(or "")

Status "In Progress" AND Finish is greater than Today, Green

Status "In Progress" AND Finish is within 2 days of Today = Yellow

Status "In Progress" AND Today is 3 or more days past Finish = Red

I must be getting caught up in the correct order of how to enter these Nested Ifs. Any help is appreciated!

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Moore

Hey @nlehmann

Try this

=IF(Status@row = "Complete", "Green", IF(Status@row = "On Hold", "Blue", IF(Finish@row = "", "", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(2), Status@row = "In Progress"), "Yellow", IF(AND(Status@row = "In Progress", Finish@row <= TODAY(3)), "Red", IF(AND(Status@row = "In Progress", Finish@row > TODAY()), "Green"))))))

Does it work for you?

Kelly

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.