Can someone please help me with an "if" formula?

Hi. Can someone please help me create an "if" formula for a "health" column with symbols. I just needs the following:

if cell in "State" column is -

  • Not started = Blue
  • in progress = yellow
  • in progress, but today is greater than "end date / due date" column = Red
  • complete = green

Thank you!


  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    The below formula is one I use in my sheets for changing the color dots to what I need it to based on statuses and time, but I have modified it to fit for your case. Try it out to see if it works for you.

    =IF(State@row = "Not started", "Blue", IF(State@row="in progress", "Yellow", IF(AND(TODAY() > [end date / due date]@row, State@row="in progress"), "Red", IF(State@row="complete", "Green"))))

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!