Project Health formula

NCharleb

Team, I always appreciate your time 🙂

Oh boy, they changed the symbols and rules on me!

I am now looking at these rules:

Added - Project Health is "blank" for any row that has a Status of "On Hold" or "Cancelled" or “Not Started”

Project Health is “blank” for any row where the Status and Target End Date cells are empty

Changed colour from green

- Project Health turns "BLUE" for any row that has a Status of "Complete"

Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", "Cancelled" or “Not Started”, and the Target End Date is within the next 7 days

Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", "Cancelled" or “Not Started”, and the Target End Date is in the past (this also had "is within the next 3 days" and when I remove it I get error #UNPARSEABLE)

Something not right. If you look at the parent line for Trust is shows "green". One child line complete and the other cancelled. I think it should be "blue" - yes?

This is the formula so far

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Complete", "Blue", IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started", Status@row = "On Hold"), "", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(3), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly Moore

Hey @NCharleb

No, as written the parent row should be green. When using IF statements, the formula progresses until the first True statement. As written, the Parent row gives a few conditions then says, if not these make it 'Green'. That's why it is Green. If the Complete in the parent row trumps all other conditions, then we move that IF statement to the front of the line.

See if this revised formula catches the changes that you need. If I missed something, shout out and we'll tweak it.

=IF(Status@row = "Complete", "Blue", IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started", Status@row = "On Hold"), "", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly

NCharleb

You are the best! Thank you for your expertise!

Kelly Moore

Hey @NCharleb

No, as written the parent row should be green. When using IF statements, the formula progresses until the first True statement. As written, the Parent row gives a few conditions then says, if not these make it 'Green'. That's why it is Green. If the Complete in the parent row trumps all other conditions, then we move that IF statement to the front of the line.

See if this revised formula catches the changes that you need. If I missed something, shout out and we'll tweak it.

=IF(Status@row = "Complete", "Blue", IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started", Status@row = "On Hold"), "", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly

NCharleb

You are the best! Thank you for your expertise!