Help with a status health formula please
Hi All, we have developed a fairly basic formula to determine the health of tasks:
=IF(Status@row = "Complete", "Green", IF(Status@row = "Canceled", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", IF(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), "Red")))))
I would really appreciate assistance with making it smarter based on these scenarios:
- If Status@row = “In Progress” and [End Date]@row < TODAY (end date in the past), show red
- If Status@row = “Not Started” and [End Date]@row < TODAY (end date in the past), show red
- If Status@row = “Not Started” and [Start Date]@row < TODAY (start date in the past), show yellow
- If Status@row = “In Progress” and [Start Date]@row >= TODAY (start date is today or in the future), show green (currently showing yellow)
Thank you.
Answers
-
This should function correctly; I have it working in a test sheet. However, there are a couple of disclaimers:
First, this could definitely be made more concise. Because you have some somewhat complex scenarios, I opted for human-readability at the expense of having a longer formula. Some conditions could definitely be combined to optimize this, but it would be slightly more difficult to follow the logic. (at least in my opinion)
Second, I had to make a few assumptions. For example, you do not specify the desired output if Status@row is "On Hold" and [End Date]@row is in the future. I assumed green in this scenario, but I could definitely see the logic in desiring a "yellow" result.
Give it a try.... it can always be tweaked if needed.
-
Not sure if you can see @Carson Penticuff 's formula and I can't for some reason, but - just in case - here's one that seems to work:
=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Green", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row >= TODAY()), "Green", "Yellow")))))
As Carson noted, not clear what you want for "On Hold" projects, but I made them yellow (they get caught by the value_if_false portion of the formula).
You could probably condense the "In Progress" and "Not Started" portions of this formula with OR statements, but I agree that human readability may be better here - especially if you need to later edit this.
-
@Danielle Arteaga Maybe I missed a copy/paste somehow? I appear to have completely left out the formula... that is somewhat embarrassing. Anyway, here is what I meant to post:
=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", [Start Date]@row >= TODAY()), AND(Status@row = "Not Started", [Start Date]@row >= TODAY()), AND(Status@row = "On Hold", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row >= TODAY()), "Yellow", IF(OR(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), AND(Status@row = "In Progress", [End Date]@row < TODAY()), AND(Status@row = "Not Started", [End Date]@row < TODAY())), "Red", "")))
-
No worries! I thought it was my browser just not rendering what you added.
Now @Nicole Ross has multiple ways to get where she's going. Can never hurt. 😁
-
Thank you so very much Danielle and Carson. Your assumptions, comments and formulas are enormously helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!