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

RogerWyatt

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.

Matt Johnson

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

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

RogerWyatt

Thank You.

Worked like a charm.