RGBY Ball and Date Formula



I am trying to write a formula that can work out the following

RED - if the start date has past and the complete % is 0

BLUE - if the start date is in the future and the complete % is 0

YELLOW - if complete % is 1-99

GREEN - if complete % is 100


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    IF formulas are the way to go. They always go from left to right and stop on the first true answer.


    IF([Complete %]@row = 1, "Green", IF(AND([Start Date]@row < Today(), [Complete %]@row = 0), "Red", IF(AND([Start Date]@row >= Today(), [Complete %]@row = 0), "Blue", "Yellow")))

    Let me know if you have any issues.

  • Aimee Colton

    Hi Mike,

    I receive #unparseable when I added the formula to a cell

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Aimee,

    Mike will likely say the same thing... but check your column names to what's written in the formula - they will need to be exact matches:

    • Complete %
    • Start Date

    If this doesn't help, could you post a screen capture of your sheet/column names? (Please remove any sensitive data) And the formula as you pasted it into your sheet?


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Yep. Always use the exact column names as they are in your headers. Screenshots of your formula and the header rows will help.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What are your header names for the start date and complete % columns?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!