Project Health calculation with parent/child rows
Hoping to get some help with calculating health of a project. Here are my parameters:
Green: Status column is "Complete" or "In Process" or the Finish date is 7 days out or further.
Yellow: Status column is not "Complete", "On Hold" or "Cancelled", and the Finish is up to 1 day in the past.
Red: Status column is not "Complete", "On Hold" or "Cancelled", and the Finish was more than 1 day 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.
Best Answer
-
Here is my take on it:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))> 0, IF(COUNTIFS(CHILDREN(), @cell = "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")> 0, "Yellow", "Green")), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Complete", [Finish Date]@row> TODAY(7)), "Green", IF([Finish Date]@row<= TODAY(-2), "Red", "Yellow"))))
You should be able to apply this as a column formula, and it should work on both parent and child rows.
Answers
-
Have you been able to successfully create the individual formulas (one for child rows and one for parent rows) and just need help combining them, or do you need help with creating the individual pieces?
-
I need help with creating the individual pieces.
-
Hey @Kristin G
Let's see if I can help you out with these. You're going to want to use nested
IF
statements with multiple conditions inside someOR
&AND
statements.First, I want to clarify what your conditions are. Here is what I interpreted:
------- Conditions -------
Green when:
- Status is "Complete" (regardless of Finish Date)
- Status is "In Process" & Finish Date is 7+ days in the future
- Status is blank & Finish Date is 7+ days in the future
Yellow when:
- Status is "In Process" & Finish Date is this week, today, or up to 1 day in the past
- Status is blank & Finish Date is this week, today, or up to 1 day in the past
Red when:
- Status is "In Process" & Finish Date is more than 1 day in the past
- Status is blank & Finish Date is more than 1 day in the past
Gray when:
- Status is "On Hold" (regardless of Finish Date)
- Status is "Canceled" (regardless of Finish Date)
Blank when:
- Both Status & Finish Date are blank
Those are the conditions I interpreted, & I believe they cover all situations. Let me know if my assumptions are wrong so I can help you further with the exact conditions.
------- Health Formula -------
Here's the formula I came up with. Note that my column names might be different than yours & you'll need to make sure they're accurate to your sheet. In the Health column, you'll want a formula similar to the following:
=IF(AND(ISBLANK(Status@row), ISBLANK([Finish Date]@row)), "", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(OR(Status@row = "Complete", AND([Finish Date]@row - TODAY() >= 7, OR(Status@row = "In Process", ISBLANK(Status@row)))), "Green", IF(AND([Finish Date]@row - TODAY() >= -1, [Finish Date]@row - TODAY() < 7, OR(Status@row = "In Process", ISBLANK(Status@row))), "Yellow", IF(AND([Finish Date]@row - TODAY() < -1, OR(Status@row = "In Process", ISBLANK(Status@row))), "Red", "")))))
Here's a screenshot of my formula in case that's easier to read:
------- Sample Results -------
Here's a screenshot of the results I got when I tried a bunch of different Status / Finish Date combinations:
I organized it by the Finish Date & added in a Notes column to make it a little easier to interpret what you're looking at.
I really hope this helps!! 😊 Feel free to mention me on here if you have any more questions. I know setting up nesting
IF
/AND
/OR
statements can be tricky.Good luck! - Ro
-
Here is my take on it:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))> 0, IF(COUNTIFS(CHILDREN(), @cell = "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")> 0, "Yellow", "Green")), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Complete", [Finish Date]@row> TODAY(7)), "Green", IF([Finish Date]@row<= TODAY(-2), "Red", "Yellow"))))
You should be able to apply this as a column formula, and it should work on both parent and child rows.
-
This was helpful - thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!