Help with a Formula for Status Change

I want to create a formula that changes the status colors to red, yellow or green. Below are the criteria:

IF Due Date is < Today and progress bar is not "Full", then "Red".

IF Due Date is > Today - 3days, and progress bar is not "Three Quarters", then "Yellow"

All other scenarios = "Green"

Any help or suggestions would be appreciated.

Thank you


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi@Armando D Garcia ,


    =IF(AND([due date]@row<today(), [Progress bar]@row <>"Full"), "Red", IF(AND([due date]@row>today(-3), [Progress bar]@row<>"three quarters"), "yellow", "green")


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!