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.

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!