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.
Best Answer
-
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.
Answers
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!