Multiple If statements that require the formula to ignore a blank cell

I am trying to create a formula that will look at a date and determine if today's date is greater than the date in a particular field it will bring back the word "expired" but if all the dates are greater than today's date it should be left blank;

The hiccup I am encountering is when the field is blank.

I have tried many formulas and the following is working; however if there is a blank field it will give me a false positive.

=IF(TODAY() > [General Liability Expiration]86, "Expired", IF(TODAY() > [Automobile Liability Expiration]86, "Expired", IF(TODAY() > [Umbrella Liability Expiration]86, "Expired", IF(TODAY() > [Workers Compensation Expiration]86, "Expired", IF(TODAY() > [Professional Liability Expiration]86, "Expired", " ")))))

So if General Liability Expiration is blank and all the other dates are in the future I still get "expired" when I should be getting a blank.

Any assistance would be greatly appreciated.

Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    I see what your saying. My bad,


    =IF(COUNTIF([General Liability Expiration]@row:[Professional Liability Expiration]@row, AND(NOT(ISBLANK(@cell)), @cell < TODAY())) > 0, "Expired")

Answers

  • Well...it worked sort of...


    I have all the columns side by side as noted above.


    However I get a blank in the insurance status (2nd). Based on the data I should be getting an "expired" status as one of the insurance policies is expired.


    The goal is if there is a "blank" to ignore it (as some companies don't require automotive or professional) however if there is a date that is expired it should bring back a status of expired in any of the columns noted above.

  • L_123
    L_123 ✭✭✭✭✭✭


    I made a mistake on the formula in the second column reference by not updated the row number. My bad. I'm to used to using @row references lol. Above is the result I get when using the formula below.


    =IF(COUNTIF([General Liability Expiration]@row:[Professional Liability Expiration]@row, AND(NOT(ISBLANK(@cell)), @cell > TODAY())) > 0, "Expired")

  • So on line #1 we should not see "expired" under the checker as the professional liability is blank but all the others are still valid.

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    I see what your saying. My bad,


    =IF(COUNTIF([General Liability Expiration]@row:[Professional Liability Expiration]@row, AND(NOT(ISBLANK(@cell)), @cell < TODAY())) > 0, "Expired")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!