IF function + TODAY + Harvey Balls.

Options

=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.

Tags:

• ✭✭✭✭✭✭
edited 11/23/22
Options

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.

• Options

Thank you Mike TV

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!