Program Health Formula

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!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!