If and symbols

Question

Hi I have a symbols (rggy) column "Overview Status" that looks at two columns Expiry Date and Overview Date. I would like that if expiry date is less than today to be gray, but if the overview date getting close to today to be yellow, red if it's late and green if it's OK. I would like the yellow warning to be a month before today. I'm not sure what I'm doing wrong with the formula IF([Expiry Date]1 < TODAY(), "Gray", IF(AND([Overview Date]1 > TODAY(-30), [Overview Date]1 < TODAY()), "Yellow", IF([Overview Date]1 >= TODAY(), "Green", IF([Overview Date]1 < TODAY(), "Red"))))

Nieves Medina · Answer

Hi

The expiry date is for a contract in which case I don't need to review the slides.  The formula sits on the overview status

The Overview Date, is a presentation document that needs periodical revision whilst the contract is live.  This document needs reviewing every 3 months for the duration of the contract.  I need to flag yellow 30 days before the overview review due, red if  the overview date is later than today and green otherwise.  I hope this helps?

Nieves

David Joyeuse · Answer

Hi @Nieves Medina

Some screenshots could help us understanding what you want to do here, and what the actual problem is with your formula, as it seems ok.

I'm not sure of what you're trying to accomplish here as we don't have any info regarding the two dates columns. Is [Expiry Date] prior or future compared to [Overview Date]? Or does it change one line to another? That thing will determine the way the formula will be articulated. If [expiry date] is always after [overview date] then your formula will only return gray until the date is passed where the formula will display green then. (And thus you'll never display Red or Yellow).

Considering [Expiry Date] is always after [Overview Date] the formula should be like this:

=IF([Expiry Date]@row>TODAY(), IF([Overview Date]@row>TODAY(), "Red", IF([Overview Date]@row>=TODAY(-30), "Yellow", "Green")), "Gray")

Here we have:

* If [Expiry Date] is in the past: "Gray"
* If [Overview Date] is in the past: "Red"
* If [Overview Date] is in the next 30 days: "Yellow"
* If [Overview Date] is in the next 31+ days: "Green"

Is that fine with you?

Hope it helped!