Health status in a project plan
Hi,
I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green, Grey based on Status and Due Date.
If the status is complete, grey.
If the status is not complete and the due date is more than 5 working dates days in the past , then yellow.
If the status is not complete and the due date is than 10 working days in the past more, then red.
If the status is blocked or on hold, flag it as red.
For reference, status column includes: in progress, not started, complete, on hold, blocked.
Otherwise, it displays Green.
Would very much appreciate a quick response!
Many thanks
Yaya
Best Answer
-
Sorry, I misspelled Gray and then had a parentheses in the wrong spot. This should do it.
=IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))
Answers
-
Something like this?
=IF(Status@row = "Complete", "grey", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10)), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))
-
Thanks a lot David. Not sure why but returns as as UNPARSEABLE.
Also within this health status, Grey doesn't seem to display as a ball, but just text.
Also, when referenced in SS, the 5, 10 are working days or calendar days?
-
Sorry, I misspelled Gray and then had a parentheses in the wrong spot. This should do it.
=IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green"))))
-
Thank you so much David. You made my dayyyyy!
-
Sorry David, two more questions:
- On the top - there are general information on the top rows, where I don't want a rag status, how to get rid of it?
- For project activity - where there are no dates yet (start nor due date), how do you normally mark this? with the current formula, it displays red?
Thanks
-
1 - You could convert from a column formula to a cell formula and copy the formula to all of the cells you want the status to appear on. Or you could make a note on the top column that the status is not indicative of anything. Either way should work.
2- You can either fill them in with "filler" dates, or we could write one more nested IF at the beginning of the formula that checks for them. So, if they don't have dates and you want the status to show green you would add:
IF(ISDATE([Due Date]@row) = 0, "Green"
This formula reads as "If the Due Date column does not contain a date, then mark the health as Green".
-
Thanks David!
Do you mind incorporated into the overall formula?
Also for tasks overdue less than 5 days, the Yellow sign doesn't seem to work, just a red? Not sure why.
-
The Yellow status works on the test sheet I've created for myself. I would need some more specifics on what you're seeing.
=IF(ISDATE([Due Date]@row) = 0, "Green", IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF([Due Date]@row <= TODAY(-10), "Red", IF([Due Date]@row <= TODAY(-5), "Yellow", "Green")))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K 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
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!