# Changing Harvey Ball Color

Options

I'm using a formula to calculate if the health color should be red, green or blue, but I also want to change it to red if the revised date is greater than the End Date, to show there is a slip in the schedule.

This is what I have that's not working:

=IF([% Comp]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red"), IF([Start Date]@row < TODAY()), IF ([Revised End Date]@row > [End Date],"RED" ), IF([% Comp]@row = 0, "Yellow", "Green"))))

Any assistance is appreciated. Thanks,

• ✭✭✭✭✭
Options

Hi Edward,

Can you describe each criteria for the Harvey ball color you'd like displayed?

I'm a bit confused by your second IF statement

`IF([End Date]@row < TODAY(), "Red"), IF([Start Date]@row < TODAY())`

• Options

Ayelet

Actually here is what I'm hoping to achieve:

IF %Comp is 100% complete change to blue

If % Comp is 1-99% and within the current timeframe, green

If % comp is below 50% and with two weeks of the End Date, yellow

IF % Comp is below 75% and one week from End Date Turn Red

If the Revised end date is after the end date (which would mean the schedule is slipping) turn red

Thanks for the assistance

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!