Health Formula, Red is Taking Over

rachelllmillerr

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

@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")))

rachelllmillerr

https://community.smartsheet.com/discussion/comment/364086#Comment_364086

I have it as "+ 10" instead of "10 +". Does that make a difference?

sharkasits

@rachelllmillerr I'm not sure I understand the question?

https://community.smartsheet.com/discussion/comment/364101#Comment_364101

Are you asking if Today() + 10 is different than 10 + Today()?

sharkasits

@rachelllmillerr Is this the result your looking for?

OR