RGY Health Formula for Status and Due Date

I'm struggling to complete a formula to do the following:

If Status is Complete, mark the health green.

If Status is Not Started or In Progress and End Date is 10 days or greater, mark health green

If Status is In Progress OR Not Started and End Date is within 5 days, mark the health yellow

If Status is Not Started OR In Progress and End Date is past due, mark the health red.

I started the formula below but I'm receiving an "Incorrect argument set" message:

=IF(Status@row = "Complete", "Green", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY(5)), "Yellow")))))

Find more posts tagged with

Formulas

Accepted answers

Brent Wilson

One of the first steps is to try and simplify your logic before you try to write the IF Statement

Looking at your logic this is What I have reduced your ask to

If STATUS is COMPLETE then Health is GREEN
1. Now looking at the rest status becomes Arbitrary as every other logic test cheques for "Not Started" OR "In Progress"
2. Because "1." Rules out the complete state everything else is either "Not Started" OR "In Progress"
So now the Check is just on the End Date. So Start from the Highest 10 days away and rule cases out leaving the Red Case Last.

This will do what you are asking

=IF([Status]@row="Complete", "Green", IF([End Date]@row > Today(10), "Green", IF([End Date]@row > Today(5), "Yellow", "Red")))

All comments

Brent Wilson

One of the first steps is to try and simplify your logic before you try to write the IF Statement

Looking at your logic this is What I have reduced your ask to

If STATUS is COMPLETE then Health is GREEN
1. Now looking at the rest status becomes Arbitrary as every other logic test cheques for "Not Started" OR "In Progress"
2. Because "1." Rules out the complete state everything else is either "Not Started" OR "In Progress"
So now the Check is just on the End Date. So Start from the Highest 10 days away and rule cases out leaving the Red Case Last.

This will do what you are asking

=IF([Status]@row="Complete", "Green", IF([End Date]@row > Today(10), "Green", IF([End Date]@row > Today(5), "Yellow", "Red")))

Tony Fronza

Thank you so much, @Brent Wilson! As usual, I was overthinking :)

Tony Fronza

@Brent Wilson I'm trying now to add IF Status is Canceled, then leave the field blank (i.e., no R, G, or Y). Here's what I wrote: =IF(Status@row = "Complete", "Green", IF (Status@row = "Canceled", "", IF([End Date]@row > TODAY(10), "Green", IF([End Date]@row > TODAY(5), "Yellow", "Red"))))

What am I missing?

Brent Wilson

Very simple one...

"IF (Status@row" should be "IF(Status@row"

Smartsheet doesn't know how to parse the statement because it is expecting the "(" right after the IF hence #UNPARSABLE

No Space !!

=IF(Status@row = "Complete", "Green", IF(Status@row = "Canceled", "", IF([End Date]@row > TODAY(10), "Green", IF([End Date]@row > TODAY(5), "Yellow", "Red"))))

Tony Fronza

Doh! Thank you so much for reviewing my request and helping me with my error.

caolm

@Brent Wilson is there a way to use this formula using business/working days instead of calendar days?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.