IF formula for date and text in the same cell.

Hi,

I currently have this formula for a cell:

=IF(AND([Quote Validity]62 < TODAY(), ISDATE([Quote Validity]62)), "EXPIRED")

However, if I need to put anything other than a date for Quote Validity cell. It returns an "#INVALID OPERATION"

What needs to be added to that formula so when a text, besides a date is inputted, it doesn't input anything (cell is just blank).

Thanks.

Find more posts tagged with

Accepted answers

Matt Johnson

Hi @SmartsheetRookie

I tested it a little further and if your "Quote Validity" column is a date column I think it works as expected. You can still enter text into that column as long as you leave the "Restrict to Dates" box unchecked.

If the date is in the past, show EXPIRED
If the date is in the future, show nothing
If the date is text, show nothing.

Also, if I'm understanding this correctly, you don't actually need the ISDATE part of the formula. The below formula is doing the same thing.

=IFERROR(IF([Quote Validity]@row < TODAY(), "EXPIRED"), "")

I hope that helps.

Matt

All comments

Matt Johnson

Hi @SmartsheetRookie

You should add an IFERROR function to the formula. It would look something like below.

=IFERROR(IF(AND([Quote Validity]62 < TODAY(), ISDATE([Quote Validity]62)), "EXPIRED"),"")

I hope that helps.

Matt

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/330703#Comment_330703

If a date is in the past, it doesn't input "EXPIRIED" now.

Matt Johnson

Hi @SmartsheetRookie

If the date is in the past, show EXPIRED
If the date is in the future, show nothing
If the date is text, show nothing.

Also, if I'm understanding this correctly, you don't actually need the ISDATE part of the formula. The below formula is doing the same thing.

=IFERROR(IF([Quote Validity]@row < TODAY(), "EXPIRED"), "")

I hope that helps.

Matt

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.