Changing Harvey Ball Color


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,


  • Ayelet Weiner
    Ayelet Weiner ✭✭✭✭✭

    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())

  • Edward Townsend


    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!