Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Change Flag Color if Due Date is Greater than today

Options
DLAGUS
DLAGUS
edited 12/09/19 in Archived 2017 Posts

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.

Comments

  • Jason Anderson
    Options

    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. 

     

     

    chrome_2017-10-24_08-40-02.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

This discussion has been closed.