Harvey Ball changes based on completion and dates

Hi, once again I feel I'm so close with this one, as I can get Blue and Green to work as expected, but Red and Yellow don't appear to want to play ball (see what I did there?).

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

What I'm looking for is if the project is complete, the Harvey Ball is blue, which if I set everything to 100% it is.

I'd like a Yellow ball to show if we're overdue but within 10 days of End Date, Red if more than 10 days overdue.

If the End Date has not yet been reached, the ball should be Green.

The formula above works for Blue and Green, yet I don't see and Red or Yellow balls, no matter how many times I change the dates in testing.

I haven't used (AND) previously, so hopefully someone can spot a simple error and point me in the right direction.

Have a great weekend all.

Find more posts tagged with

Accepted answers

James Keuning

Here is how I attack these problems: I create one column for each condition that I want to test, and I have all of the values in a single column. Then I can create one row with a value that contains a value that should trigger the correct response for a respective column. Once I have all of those returning values correctly, I string together the IF statement.

So create a column and put this in it:

IF(AND([End Date]30 > TODAY() + 10, [% Complete]30 < 1), "Red"

And then put values in End Date and %Complete that should satisfy that statement, Do you get Red?

If you don't post back here what values you used and what result, if any, you get.

All comments

James Keuning

So create a column and put this in it:

IF(AND([End Date]30 > TODAY() + 10, [% Complete]30 < 1), "Red"

And then put values in End Date and %Complete that should satisfy that statement, Do you get Red?

If you don't post back here what values you used and what result, if any, you get.

PM_Reeves

Hi @James Keuning,

Your answer made me look at it in a different way, so thanks for that - it gave me the clarity I needed to break it down.

The formula 'worked' but the fact that I had "Green" as the first condition was over-riding the Red and Yellow. Once I moved the "Green" rule to before the "Blue" it worked a charm.

Thanks for your insight of treating each as individual condition, rather than as a whole.

Enjoy your day.

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.