How to check Finish date and %Complete to determine RYG symbol?


Hi Expert,

Seeking advice how can I come out with a multi-condition formula from 2 columns with below criteria? Say I have a column name %Complete and another column - Finish Date

If %Complete not 100% and Finish Date is smaller than today(), show Red symbol

If Finish Date is larger than today(), show Green symbol

If Today () - Finish Date more than 3 days, show Yellow symbol

If %Complete = 100%, show Gray symbol

Many thanks.


Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    It's all about Nested Ifs. I may have misinterpreted your conditions, but this formula does the following:

    Grey if % complete is 100%

    Yellow if Today - Finish Date is within 3 days

    Red if Complete is not 100% and the Finish Date is past today's date

    Green if the Complete is not 100% and Finish Date is before today's date

    =IF([%Complete]@row = 1, "Gray", IF([Finish Date]@row > TODAY(), "Red", IF(TODAY() - [Finish Date]@row <= 3, "Yellow", "Green")))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!