Health formula
I've got myself going round and round trying to create a good health column formula for the project plan shown below. Hoping you can help with a formula to achieve this for each row/task:
Green: Status column is "Complete" or the Target Finish date is 7 days out or further.
Yellow: Status column is not "Complete", "On Hold" or "Cancelled", and the Target Finish is within the next 7 days.
Red: Status column is not "Complete", "On Hold" or "Cancelled", and the Target Finish is within the next 2 days or is in the past.
Gray: Status column is "On Hold" or "Cancelled"
Blank: Health column is blank if the Status column and Target Finish columns are both empty.
If it's a parent row it shows a red health if any of the children rows under it have a red health, yellow health if any of the children rows show yellow (but not a red), and green if all children are green.
I tried following the formula on this post, but I keep getting an #UNPARSABLE error:
Project Health Formula Help — Smartsheet Community
Thanks in advance for the help.
Best Answer
-
@Mike Tomei here is the final
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray"))))))
Answers
-
@Mike Tomei try this
=IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row = TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray")))))
-
Lindsay, thanks so much for the super fast response. I feel like you're very close, but I'm seeing a lingering issue when using that formula. I can't get the health cell to turn green. Instead it goes blan. The yellow and red functionality is working as intended, though. I also wanted to ask if it's possible to include this functionality in the formula?:
If it's a parent row it shows a red health if any of the children rows under it have a red health, shows yellow health if any of the children rows show yellow (but not a red), and green if all children are green.
Thanks!
-
Oops, forgot one sign. Here you go. This will get you started. The other portion is possible, but I will need some time as I have project to finish up. Hold tight and I will get it to you.
=IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray")))))
-
@Mike Tomei here is the final
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray"))))))
-
Lindsay, thanks so much for the formula. Works like a charm.
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!