IF function + TODAY + Harvey Balls.

Indira Buike

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.

Mike TV

@Indira Buike

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.

Indira Buike

Thank you Mike TV