Formula for Project Health

ABice Norton · 01/29/21

Hi All,

I'm a newer user and trying to create a few formulas to support the Health of Projects Due.

First, I'd like to use simple logic to provide row health.

If Status:

Not Started AND is greater than 14 days out from Due Date = Gray
On Track to Deliver AND is 2 days out from Due Date - Green
Delayed OR 2 days out from Due Date -Yellow
Delayed AND/OR is Today - Red
Complete - Blank

Next, I'd like to roll the average of the results of these ancestors up to the parent row.

Can anyone please recommend the best way to accomplish this?

Thank you!

Kelly Moore · 01/30/21

Hey @ABice

I changed your Gray criteria to anything outside of 14d - you didn't have any criteria that allowed for days outside of 14d and other status' besides "Not Started". As you find other criteria not represented, we can tweak the formula

=IF(Status@row = "Complete", "", IF([Due Date]@row >= TODAY(14), "Gray", IF(AND(Status@row = "Delayed", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "On Track to Deliver", [Due Date]@row <= TODAY(2)), "Green", IF(OR(Status@row = "Delayed", [Due Date]@row <= TODAY(2)), "Yellow")))))

You asked also about rolling the status of the children up to Parent rows. Here is one approach. The formula below first looks to see if the row is a parent row, then checks to see if any reds, then any yellows, or finally all green. If it isn't a parent row, it executes the formula above.

=IF(COUNT(CHILDREN([primary row]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))), IF(Status@row = "Complete", "", IF([Due Date]@row >= TODAY(14), "Gray", IF(AND(Status@row = "Delayed", [Due Date]@row = TODAY()), "Red", IF(AND(Status@row = "On Track to Deliver", [Due Date]@row <= TODAY(2)), "Green", IF(OR(Status@row = "Delayed", [Due Date]@row <= TODAY(2)), "Yellow"))))))

Don't forget to change the column names to match your column names.

Kelly

Formula for Project Health

Answers

Help Article Resources

Categories