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

Angeline Lai

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.

David Tutwiler

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")))

David Tutwiler

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")))

Angeline Lai

Thanks David! It is working. Appreciated it. 😀

Angeline Lai

https://community.smartsheet.com/discussion/comment/279163#Comment_279163

Apologies. Only Gray and Red working but not Yellow/ Green. Wonder why? I tried editing the days but for criterai today - minus <=-3., either return yellow or all become green for all. No differentiator even in task <= -3 versus task more than 3 days ...Pls. advise where did I go wrong? pls. refer my snapshot. Many Thanks

Angeline Lai

Finish 24 Feb - show Yellow

Finish 8 Mar - should show Green but showing Yellow 🙄

David Tutwiler

Try this:

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

Angeline Lai

Hi David,

Seem no luck :( Now everything turn Red ...hmmm.. is there anyway to test and debug the function? Thanks