Formula to auto populate Health based on End Date & Status columns
I can get the formula to work in pieces, but not as a whole...
My formula: =IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "", IF(Status@row = "In Progress", "Green", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF([End Date]@row < TODAY(7), "Yellow", IF([End Date]@row < TODAY(), "Red")))))))
What I want:
If Status is Complete - Health is Green
If Status has Not Started - Health is blank
If Status is On Hold - Health is Blue
If Status is At Risk - Health is Yellow
If Status is Delayed - Health is Red
If Status is In Progress (and over 7 days from End Date) - Health is Green
If Status is In Progress and within 7 days of End Date - Health is Yellow
If Status is In Progress and End Date is today or has passed - Health is Red
Thank you in advance!
Best Answer
-
Here's another approach using AND() and OR() functions to combine the multiple criteria for green, yellow, and red symbols. (Documentation for Smartsheet functions can be found at https://help.smartsheet.com/functions .)
Below are the conditions written in expression form...
BLANK when Status is Not Started
Status@row = "Not Started"
BLUE when Status is On Hold
Status@row = "On Hold"
GREEN when Status is Complete OR Status is In Progress (and over 7 days from End Date)
OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7)))
YELLOW when Status is At Risk OR Status is In Progress and within 7 days of End Date
OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()) ))
RED when Status is Delayed OR Status is In Progress and End Date is today or has passed
OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY())))
... insert the conditions into an IF() function.
The formula...
=IF(Status@row = "Not Started", "", IF(Status@row = "On Hold", "Blue", IF(OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7))), "Green", IF(OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()))), "Yellow", IF(OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY()))), "Red", "")))))
The result...
Cheers!
Answers
-
From what I can see your issue is that the status never turns Red. That's because you need to switch this:
IF([End Date]@row < TODAY(), "Red"
To be in front of this:
IF([End Date]@row < TODAY(7), "Yellow"
Does that solve your issue?
-
=IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "", IF(Status@row = "In Progress", "Green", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow")))))))
-
Here's another approach using AND() and OR() functions to combine the multiple criteria for green, yellow, and red symbols. (Documentation for Smartsheet functions can be found at https://help.smartsheet.com/functions .)
Below are the conditions written in expression form...
BLANK when Status is Not Started
Status@row = "Not Started"
BLUE when Status is On Hold
Status@row = "On Hold"
GREEN when Status is Complete OR Status is In Progress (and over 7 days from End Date)
OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7)))
YELLOW when Status is At Risk OR Status is In Progress and within 7 days of End Date
OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()) ))
RED when Status is Delayed OR Status is In Progress and End Date is today or has passed
OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY())))
... insert the conditions into an IF() function.
The formula...
=IF(Status@row = "Not Started", "", IF(Status@row = "On Hold", "Blue", IF(OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7))), "Green", IF(OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()))), "Yellow", IF(OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY()))), "Red", "")))))
The result...
Cheers!
-
Thank you everyone for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!