Updating the Red,Green,Yellow Status Column based on Dates from End date Column

Options

I'm trying to create a formula that changes my status column to the red, yellow, red symbols.

I want to base it on my end date column.

I looked at some examples on the site, but i couldn't quite get it to work on my sheet. I was trying to set up a formula around the following parameters.

Red= Past End Date

Yellow= Within 90 Days of the End Date

Green= more than 90 days until End Date.

Also, there are some cells with N/A. will that affect some formulas?

Thanks for any help.

Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @RogerWyatt

    I think this is the formula you are looking for.

    =IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(90), "Yellow", "Green")), "")

    There's probably some other variations of this that'll work too.

    Quick note: If you are new to column formulas, this is a great use case for that. Right click on the column itself and choose "Convert to Column Formula".

    I hope this helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @RogerWyatt

    I think this is the formula you are looking for.

    =IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(90), "Yellow", "Green")), "")

    There's probably some other variations of this that'll work too.

    Quick note: If you are new to column formulas, this is a great use case for that. Right click on the column itself and choose "Convert to Column Formula".

    I hope this helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • RogerWyatt
    Options

    Thank You.

    Worked like a charm.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!