Project Health calculation with parent/child rows

Kristin G

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.

Paul Newcome

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.

Paul Newcome

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?

Kristin G

I need help with creating the individual pieces.

ro.fei

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 some OR & 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

Paul Newcome

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.

Kristin G

This was helpful - thanks!

Paul Newcome

Happy to help. 👍️