IF OR/AND Function - Considering Dates (Future, Past etc)

Brianne

Hi Team - I have a bit of a doozy.. at least it is for me. I have a sheet that needs a status populated considering 2 different date columns.

If either "Expiry Date" or "Expiry Date 2" is in the past we need a "Red" status

If either "Expiry Date" or "Expiry Date 2" columns are within the next 30 days "Yellow" Status

If both "Expiry Date" or "Expiry Date 2" columns are further than 30 days away "green" Status. Additionally if the columns are blank then the status should be blank.

This is what I have so far but it is not populating a status and I am getting an #unparseable.

=IF([Expiry Date]@row = "", "", IF([Expiry Date 2]@row = "", IF(OR([Expiry Date 2]@row < TODAY(), [Expiry Date]@row < TODAY()), "Red", IF(OR([Expiry Date]@row >= TODAY(), [Expiry Date 2]@row >= TODAY(), ([Expiry Date]@row < TODAY(+30), [Expiry Date 2]@row < TODAY(+30))), "Yellow", "Green"))))

Thank you in advance for your assistance. :)

jmyzk_cloudsmart_jp

Hi @Brianne

As shown in the demo sheet below, the following formula is working;

=IF(OR(AND(ISDATE([Expiry Date]@row), [Expiry Date]@row < TODAY()), AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row < TODAY())), "Red", IF(OR(AND(ISDATE([Expiry Date]@row), [Expiry Date]@row <= TODAY(30)), AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row <= TODAY(30))), "Yellow", IF(AND(ISBLANK([Expiry Date]@row), ISBLANK([Expiry Date 2]@row)), "", "Green")))

https://app.smartsheet.com/b/publish?EQBCT=3da68f887dc448d4a2bdbe982b24bedc

The same formula in readable format

=IF(OR(

AND(ISDATE([Expiry Date]@row), [Expiry Date]@row < TODAY()),

AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row < TODAY())),

"Red",

IF(OR(

AND(ISDATE([Expiry Date]@row), [Expiry Date]@row <= TODAY(30)),

AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row <= TODAY(30))),

"Yellow",

IF(

AND(ISBLANK([Expiry Date]@row), ISBLANK([Expiry Date 2]@row)), "",

"Green")))

Meaning of the form

If any expiry date is past and valid*	the status is "Red".
If any expiry date is within the next 30 days and valid	the status is "Yellow".
If both expiry dates are blank	no status is given (empty string).
Otherwise	the status is "Green".

(*) If an expiry date column is empty or contains a non-date value, the condition [expiry date]@row < TODAY() returns true. Therefore, we must ensure the column contains a valid date using ISDATE.

jmyzk_cloudsmart_jp

Hi @Brianne

As shown in the demo sheet below, the following formula is working;

=IF(OR(AND(ISDATE([Expiry Date]@row), [Expiry Date]@row < TODAY()), AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row < TODAY())), "Red", IF(OR(AND(ISDATE([Expiry Date]@row), [Expiry Date]@row <= TODAY(30)), AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row <= TODAY(30))), "Yellow", IF(AND(ISBLANK([Expiry Date]@row), ISBLANK([Expiry Date 2]@row)), "", "Green")))

https://app.smartsheet.com/b/publish?EQBCT=3da68f887dc448d4a2bdbe982b24bedc

The same formula in readable format

=IF(OR(

AND(ISDATE([Expiry Date]@row), [Expiry Date]@row < TODAY()),

AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row < TODAY())),

"Red",

IF(OR(

AND(ISDATE([Expiry Date]@row), [Expiry Date]@row <= TODAY(30)),

AND(ISDATE([Expiry Date 2]@row), [Expiry Date 2]@row <= TODAY(30))),

"Yellow",

IF(

AND(ISBLANK([Expiry Date]@row), ISBLANK([Expiry Date 2]@row)), "",

"Green")))

Meaning of the form

If any expiry date is past and valid*	the status is "Red".
If any expiry date is within the next 30 days and valid	the status is "Yellow".
If both expiry dates are blank	no status is given (empty string).
Otherwise	the status is "Green".

(*) If an expiry date column is empty or contains a non-date value, the condition [expiry date]@row < TODAY() returns true. Therefore, we must ensure the column contains a valid date using ISDATE.