Change Flag Color if Due Date is Greater than today

DLAGUS

Hello everyone Very new in smarsheet and I have this problem I have basically a due date column and would want another column (flag field or RYG) to turn red once the due date is > today's date.

 

I have been getting #UNPARSEABLE errors everytime

=IF(AND([End Date]2 <= TODAY(), [End Date]2 >= TODAY(), RED)

 

Please help.

Jason Anderson

This can be done with nested IF statements..

=IF([End date]5 > TODAY(), "Red", IF([End date]5 = TODAY(), "Yellow", IF([End date]5 < TODAY(), "Green")))

Seen in the top of my test sheet

I also have conditional formatting on the sheet to tint the RYG cells their color as well. 

 

 

J. Craig Williams

DLAGUS,

Your original formula had a few problems

=IF(AND([End Date]2 <= TODAY(), [End Date]2 >= TODAY(), RED)

You are missing a closing parentheses for the AND(). 

It would have looked like this:

=IF(AND([End Date]2 <= TODAY(), [End Date]2 >= TODAY()  ) , RED)

but if you want past due, you don't want an AND anyway.

=IF([End Date]2 >= TODAY(), RED)

As you can see from Jason's answer, the value of a symbol field (like a color) is Text, so "Red" not RED

=IF([End Date]2 >= TODAY(), "Red")

And that gets you want you asked for. As Jason's post shows, if you want to have all the RYG possibilities in your formula, you'll use Nested IF statements

Lastly, if you wanted to use the Flag symbols, the values expected are true/false or 1/0. All of these work:

=IF([End Date]2 >= TODAY(), true, false)

=IF([End Date]2 >= TODAY(), 1, 0)

=IF([End Date]2 >= TODAY(), 1)

though the last two are more common answers on the Community.

Craig