Overall Project Health (Parent Row) Formula based on Children Rows

Hannah Gray

Hello!

I am currently using this formula to visual task health.

=IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row > TODAY(), "Green"))))

The tasks are nestled under a parent row for a given project. Is there a way to make the parent rows turn red if any of the tasks below are red? Right now they stay green since the overall project is still considered on time, however we want to be flagging a project if any of it's tasks are overdue.

In this example, I would want the project "Magnifica" to be red since some of the child rows are red.

Appreciate any help here!

Nick Korna

Hi @Hannah Gray,

This is doable - if you only want the parent to be red if any of the children are, but green otherwise then this column formula in Health should work:

=IF(COUNTIF(CHILDREN([Health]@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Health]@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))).

If you want the parent to be yellow if there are no reds, but at least one yellow:

=IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))))

Examples, with the first formula in Health column, second in Health2:

With a red status added:

Hope this helps - if you have any problems or questions with this let us know. 😊

Nick Korna

Hi @Hannah Gray,

This is doable - if you only want the parent to be red if any of the children are, but green otherwise then this column formula in Health should work:

=IF(COUNTIF(CHILDREN([Health]@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Health]@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))).

If you want the parent to be yellow if there are no reds, but at least one yellow:

=IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", Status@row = "Done", Status@row = "Approved"), "Green", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(), "Green")))))))

Examples, with the first formula in Health column, second in Health2:

With a red status added:

Hope this helps - if you have any problems or questions with this let us know. 😊

Hannah Gray

Worked perfectly, thank you!