Formula for Project Health
Hi All,
I'm a newer user and trying to create a few formulas to support the Health of Projects Due.
First, I'd like to use simple logic to provide row health.
If Status:
- Not Started AND is greater than 14 days out from Due Date = Gray
- On Track to Deliver AND is 2 days out from Due Date - Green
- Delayed OR 2 days out from Due Date -Yellow
- Delayed AND/OR is Today - Red
- Complete - Blank
Next, I'd like to roll the average of the results of these ancestors up to the parent row.
Can anyone please recommend the best way to accomplish this?
Thank you!
Answers
-
Hey @ABice
I changed your Gray criteria to anything outside of 14d - you didn't have any criteria that allowed for days outside of 14d and other status' besides "Not Started". As you find other criteria not represented, we can tweak the formula
=IF(Status@row = "Complete", "", IF([Due Date]@row >= TODAY(14), "Gray", IF(AND(Status@row = "Delayed", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "On Track to Deliver", [Due Date]@row <= TODAY(2)), "Green", IF(OR(Status@row = "Delayed", [Due Date]@row <= TODAY(2)), "Yellow")))))
You asked also about rolling the status of the children up to Parent rows. Here is one approach. The formula below first looks to see if the row is a parent row, then checks to see if any reds, then any yellows, or finally all green. If it isn't a parent row, it executes the formula above.
=IF(COUNT(CHILDREN([primary row]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))), IF(Status@row = "Complete", "", IF([Due Date]@row >= TODAY(14), "Gray", IF(AND(Status@row = "Delayed", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "On Track to Deliver", [Due Date]@row <= TODAY(2)), "Green", IF(OR(Status@row = "Delayed", [Due Date]@row <= TODAY(2)), "Yellow"))))))
Don't forget to change the column names to match your column names.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!