Formula Edit to default to blue health when dates are blank

We are currently using this in project plans to establish health. We would like to default items without date values to Blue instead of Red when a project is initially provisioned. This way our portfolio reporting isn't showing a bunch of Red unnecessarily when things are initiated.

=IF(OR(Level@row = 0, Level@row = 1), 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(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY(7)), "Yellow", "Green"))))

Find more posts tagged with

Accepted answers

Paul Newcome

Try this...

=IF([End Date]@row = "", "Blue", IF(OR(Level@row = 0, Level@row = 1), 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(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY(7)), "Yellow", "Green")))))

All comments

Paul Newcome

Try this...

KCurry

It is inserting the word "Blue" instead of the colored circle?

Paul Newcome

Make sure your column settings are set to the appropriate symbols. There are options for RYG, RYGG, and RYGB.

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.