Health for Parent Row Based on Health of Child Rows

Josh Martin

Hello, I need some assistance combining the following formulas into a single Column Formula.

Formula 1:

• Successfully automates Health for Tasks based on desired criteria.
• Should only apply if the row is a Task.

Formula 2:

• Successfully automates Health for Milestones & Project Components based on desired criteria.
• Should only apply if the row is either a Milestone or a Project Component.

Problem:

• The above formulas need to be combined into a single Column Formula in order to fully automate Health for Tasks, Milestones, and Project Components across the Project Schedule.

Notes:

• The Project Schedule defines and formats Milestones and Project Components based on nesting and the use of Checkbox Columns.

Any assistance in combining these formulas would be greatly appreciated. Thank you.

Josh Martin

Problem solved. I've also incorporated automation to:

• Automate Health aggregation one level higher (i.e., Project Name).
• Automate Health for Milestones without nested Tasks to act as if they were Child rows.
• Automate Health for Tasks with Sub-Tasks to act as if they were Parent rows.

Completed Formula:

=IF(AND(OR(Project@row = 1, Component@row = 1, Milestone@row = 1), COUNT(CHILDREN()) > 0), (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(COUNT(CHILDREN()) > 0, (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))))))

Columns Required:

• Column Name = Project // Column Type = Checkbox
• Column Name = Component // Column Type = Checkbox
• Column Name = Milestone // Column Type = Checkbox
• Column Name = Health // Column Type = Symbols (4 Colors for Red, Green, Yellow, & Blue)
• Column Name = Status // Column Type = Dropdown List (6 Statuses for "Blank," "Not Started," "In Progress," "Requires Review," "At Risk," "Completed," & "Descoped")
• Column Name = Start Date // Column Type = Date/Time
• Column Name = End Date // Column Type = Date/Time
• Column Name = Duration // Column Type = Duration

Josh Martin

Text for the Formulas included here to assist with modifications:

Formula 1:

=IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))

Formula 2:

=IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))

Josh Martin

Problem solved. I've also incorporated automation to:

• Automate Health aggregation one level higher (i.e., Project Name).
• Automate Health for Milestones without nested Tasks to act as if they were Child rows.
• Automate Health for Tasks with Sub-Tasks to act as if they were Parent rows.

Completed Formula:

=IF(AND(OR(Project@row = 1, Component@row = 1, Milestone@row = 1), COUNT(CHILDREN()) > 0), (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(COUNT(CHILDREN()) > 0, (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))))))

Columns Required:

• Column Name = Project // Column Type = Checkbox
• Column Name = Component // Column Type = Checkbox
• Column Name = Milestone // Column Type = Checkbox
• Column Name = Health // Column Type = Symbols (4 Colors for Red, Green, Yellow, & Blue)
• Column Name = Status // Column Type = Dropdown List (6 Statuses for "Blank," "Not Started," "In Progress," "Requires Review," "At Risk," "Completed," & "Descoped")
• Column Name = Start Date // Column Type = Date/Time
• Column Name = End Date // Column Type = Date/Time
• Column Name = Duration // Column Type = Duration

Genevieve P.

Wow, thank you for sharing your solution!!