Health formula

Mike Tomei

I've got myself going round and round trying to create a good health column formula for the project plan shown below. Hoping you can help with a formula to achieve this for each row/task:

Green: Status column is "Complete" or the Target Finish date is 7 days out or further.

Yellow: Status column is not "Complete", "On Hold" or "Cancelled", and the Target Finish is within the next 7 days.

Red: Status column is not "Complete", "On Hold" or "Cancelled", and the Target Finish is within the next 2 days or is in the past.

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

Blank: Health column is blank if the Status column and Target Finish columns are both empty.

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.

I tried following the formula on this post, but I keep getting an #UNPARSABLE error:

Project Health Formula Help — Smartsheet Community

Thanks in advance for the help.

Lindsay AR

@Mike Tomei here is the final

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray"))))))

Lindsay AR

@Mike Tomei try this

=IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row = TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray")))))

Mike Tomei

Lindsay, thanks so much for the super fast response. I feel like you're very close, but I'm seeing a lingering issue when using that formula. I can't get the health cell to turn green. Instead it goes blan. The yellow and red functionality is working as intended, though. I also wanted to ask if it's possible to include this functionality in the formula?:

If it's a parent row it shows a red health if any of the children rows under it have a red health, shows yellow health if any of the children rows show yellow (but not a red), and green if all children are green.

Thanks!

Lindsay AR

Oops, forgot one sign. Here you go. This will get you started. The other portion is possible, but I will need some time as I have project to finish up. Hold tight and I will get it to you.

=IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray")))))

Lindsay AR

@Mike Tomei here is the final

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISBLANK([Target Finish]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [Target Finish]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(7), [Target Finish]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [Target Finish]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray"))))))

Mike Tomei

Lindsay, thanks so much for the formula. Works like a charm.