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

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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

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.

• ✭✭✭✭✭✭
Options

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

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!