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")))

Smartsheet

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 past and valid*	If any expiry date is within the next 30 days and valid	the status is "Red".	the status is "Yellow".
If any expiry date is past and valid*	If both expiry dates are blank	the status is "Red".	no status is given (empty string).
If any expiry date is past and valid*	Otherwise	the status is "Red".	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")))

Smartsheet

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 past and valid*	If any expiry date is within the next 30 days and valid	the status is "Red".	the status is "Yellow".
If any expiry date is past and valid*	If both expiry dates are blank	the status is "Red".	no status is given (empty string).
If any expiry date is past and valid*	Otherwise	the status is "Red".	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.