IF function + TODAY + Harvey Balls.
Hello I had this formula
=IF(AND([PM % Complete]@row = 1), "Green", IF(AND([PM % Complete]@row < 0.75, Finish@row = TODAY(+5)), "Yellow", IF(AND([PM % Complete]@row < 1, Finish@row = TODAY(+2)), "Red", "Green")))
Which was working all right, but now it's not working correctly, specially when the FINISH date has past the ball doesn't turn RED, it remains GREEN.
So i was trying to use the TODAY function and used this formula.
=IF(AND([PM % Complete]@row = 1), "Green", IF(AND([PM % Complete]@row < 0.75, Finish@row = TODAY(+5)), "Yellow", IF(AND([PM % Complete]@row <> 1, TODAY() - Finish@row < 0, "Red", ""))))
But it says INCORRECT ARGUMENT, can please someone help me figure it out?
What I want is the Harvey ball to be GREEN when the PM% is 100%
To be YELLOW when the PM% is more than 75% and the finish date is less than today or 5 days past due.
and RED when the PM% is less than 75% and the Finish date has passed.
Please help.
Answers
-
Are these the results you're looking for?
=IF([PM % Complete]@row = 1, "Green", IF(AND([PM % Complete]@row > 0.75, Finish@row < TODAY()), "Yellow", IF(AND([PM % Complete]@row < 0.75, Finish@row < TODAY()), "Red")))
I didn't see the difference on the Yellow color for whether the Finish date was in the past or 5 days in the past so I just used in the past by itself.
-
Thank you Mike TV
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!