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")))))
Best Answer
-
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
- Now looking at the rest status becomes Arbitrary as every other logic test cheques for "Not Started" OR "In Progress"
- 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")))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
- If STATUS is COMPLETE then Health is GREEN
Answers
-
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
- Now looking at the rest status becomes Arbitrary as every other logic test cheques for "Not Started" OR "In Progress"
- 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")))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
- If STATUS is COMPLETE then Health is GREEN
-
Thank you so much, @Brent Wilson! As usual, I was overthinking :)
-
@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?
-
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
-
Doh! Thank you so much for reviewing my request and helping me with my error.
-
@Brent Wilson is there a way to use this formula using business/working days instead of calendar days?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!