# RGY Health Formula for Status and Due Date

Options
✭✭✭✭

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

Tags:

• ✭✭✭✭✭
Options

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

1. 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"
2. 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")))

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭✭
Options

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

1. 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"
2. 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")))

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

@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?

• ✭✭✭✭✭
Options

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

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!