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

Thank you in advance for your assistance. :)

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    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")))

    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.

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    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")))

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!