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"))))))
