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.

Find more posts tagged with

Accepted answers

L_123

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

L_123

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

All comments

L_123

=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

Lilia Smith

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

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

Lilia Smith

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.