Health Formula, Red is Taking Over

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

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.