Project Health Formula Help
Hello,
I've been struggling with this formula. I borrowed parts from other project tracking templates, but I can't seem to get everything to work together; I keep getting error messages. I'm looking to:
- Project Health turns "Green" for any row that has a Status of "Complete" or if the Target End Date is 1 week out or further
- Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Target End Date is within the next 7 days
- Project Health turns "Gray" for any row that has a Status of "On Hold" or "Canceled"
- Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Target End Date is within the next 3 days or is in the past
- Project Health is “blank” for any row where the Status and Target End Date cells are empty
- Additionally, if the row has a hierarchy of “1” it should show the average health of its children tasks.
=IF(Hierarchy@row = 1, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Red") = 0, COUNTIF(CHILDREN(), "Yellow") > 0), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Yellow") = 0, COUNTIF(CHILDREN(), "Green") > 0), "Green", "Gray"))),=IF(Status@row = "Complete", "Green", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray",IF(ISBLANK(Status@row),””,IF(ISBLANK([Target End Date]),””, IF([Target End Date]@row< TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))
TIA for any help – I’m so formula illiterate.
Thank you!
Best Answer
-
This should fix that...
=IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))))
Answers
-
You are actually pretty close. Lets give this a whirl...
=IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))
-
Thank you, Paul! I'm no longer getting the error message and it appears to be working as intended. :)
One last question: is there a way to tell the cell to appear "blank" if the Status and Target End Date cells are empty? It's just so I can copy down the formula for future entries without having a column of red circles.
Thank you!
-
That is tucked away on the child row portion which should be feeding up to the parent rows. Is that not working?
-
It doesn't look like it, but I could be doing something incorrectly? I added the formula to the first cell under the Health column, and then I copied it down the column. The health circle in the blank rows is appearing as red...
-
Try this one...
=IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green"))))))
-
That fixed the blank cell issue, thank you! I did notice that the cells with a Status of "Complete" but no Target End Date appear blank now?
I'm sorry - I feel like it's so close now...
Thank you for the help!
-
This should fix that...
=IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))))
-
For some reason, the On Hold and Canceled statuses were affected (not showing Gray anymore), but I just added that part to the beginning, and that seemed to solve the issue.
I ended up with this, and it appears to be working!
=IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row <= TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))))))
Thank you very much for all of the help!
-
Glad you were able to get it working. Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!