Health Formula, Red is Taking Over

rachelllmillerr · 02/23/23

Hello!

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!!

sharkasits · 02/23/23

@rachelllmillerr

I think the issue you're having is in your @row section. You are only setting to Green if it's exactly 10 days out instead of 10 days +, and yellow if it's exactly 3 days out instead of 3-9.

Try updating that section to this....

IF([% Complete]@row = 1, "Blue", IF([End Date]@row > TODAY() + 9, "Green", IF(AND([End Date]@row > TODAY() + 2, [End Date]@row < TODAY()+10) , "Yellow", "Red")))