Formula for changing symbol colors

I created a column that changes symbol colors depending on the start date column. The below formula I put together after reading through some other posts within this community works alright except for the bit to have the symbol change to gray when the start date is today or past. What am I missing?

=IF([Start Date (ET)]@row < TODAY(14), "Red", IF([Start Date (ET)]@row < TODAY(21), "Yellow",IF([Start Date (ET)]@row <= TODAY(), "Gray", "Green")))



  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 05/15/23

    Hi @Amwil, your condition "<TODAY(14)" will always be met before your condition "<=TODAY()". Rearrange to put the gray at the start. Always check the order of your criteria -- the first "IF" that meets it's criteria will trigger first.

    =IF([Start Date (ET)]@row <= TODAY(), "Gray", IF([Start Date (ET)]@row < TODAY(14), "Red", IF([Start Date (ET)]@row < TODAY(21), "Yellow", "Green")))

  • Amwil
    Amwil ✭✭

    @Lucas Rayala noted, thank you for the assistance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!