Program Health Formula

Stephen Braunewell

Hi everyone. I am struggling to figure out a formula for program health that doesnt come back as #UNPARSABLE

Green: Status column is "Completed" or the End Date is 30 days out or further.

Yellow: Status column is "In Progress" and the End Date is within the next 30 days.

Red: Status column is "In Progress" and the End Date is within the next 7 days or is in the past.

Gray: Status column is "On Hold" or "Cancelled"

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.

Any help is greatly appreciated!

Paul Newcome

You are going to need something like this:

=IF(COUNT(CHILDREN()) = 0, IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(OR([End Date]@row>= TODAY(30), Status@row = "Completed"), "Green", IF([End Date]@row<= TODAY(7), "Red", "Yellow"))), IF(COUNTIFS(CHILDREN(), @cell = "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), @cell = "Green")> 0, "Green", "Gray"))))

Stephen Braunewell

You rock, @Paul Newcome! This worked. Any idea on how to make the cell become the corresponding color? Right now the text box just says "Red"

Paul Newcome

You would change it over to a Symbol type column and select the RYGG symbols.