Health Formula, Red is Taking Over



After taking the Smartsheet training covering advanced formulas, I've implemented the level, ancestry, and health formulas into my projects. This is my formula (=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue"))), IF([% Complete]@row = 1, "Blue", IF([End Date]@row = TODAY() + 10, "Green", IF([End Date]@row = TODAY() + 3, "Yellow", "Red"))))

Green = 10 days out, yellow = 3 days out, red should = due or overdue, and blue = completed. The issue is that the "Red" is overtaking the green and yellow in most of my sheets now.

How can I fix my formulas so the red and yellow time frames are reflected?

Thank you!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!