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

Lilia Smith

Sorry for the 2nd post; however the answer provided worked "somewhat".

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.

The answer provided was:

In smartsheet =if(today()>[column2]1,1,0) will output a 1 if the cell in [column2]1 is blank as it reads today is greater than blank. The long way to solve this is to check if each cell is blank prior to seeing how it correlates to the today value. That said if your columns general liability expiration -> Professional liability Expiration are are sequential (next to each other) there is a way around this long formula.

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

This solution requires each of your expiration columns to be next to each other with general liability expiration on the left side, and professional liability expiration on the far right.

If this solution doesn't work for you, you can always brute force this by using isblank.

=if(and(not(isblank([General Liability Expiration]86)),today()>[General Liability Expiration]86

For a starter

I tried it and the results are noted below:

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.

Paul Newcome

Try something like this...

=IF(MIN(COLLECT([General Liability Expiration]@row:[Professional Liability Expiration]@row, [General Liability Expiration]@row:[Professional Liability Expiration]@row, <> "")) < TODAY(), "Expired")

Since you only need one date to be in the past to flag "Expired", we can pull the earliest date using the MIN function. We use the COLLECT function to specify that it not be blank.

Now that we have the earliest date that is not blank, we say that IF it is LESS THAN TODAY(), "Expired".