Formula to check box if cell contains Date OR Text

I have a nested formula that currently involves one check box be checked and three date columns to contain dates in order to check-mark as 'Complete.' However, I discovered one of the date columns involved may contain text from time to time (as a date is not always applicable to this particular field). How can I revise my formula to mark a file complete if this cell contains a date OR text? Please help! Formulas are not my forte! Also - the column name in question is 'Original Docs Returned Date' See photo example

Original Formula:

=IF(AND(([Collateral Perfected]6 = 1), ISDATE([Date Loan Checked Back]6), ISDATE([Original Docs Returned Date]6), ISDATE([Imaging Date]6)), 1, 0)

Find more posts tagged with

Sheets

Finance

Formulas

functionality

Accepted answers

Paul Newcome

Instead of adding the ISTEXT for each of the columns, you can replace each of the ISDATE criteria to just say if the cell is not blank.

=IF(AND(([Collateral Perfected]6 = 1), [Date Loan Checked Back]6 <> "", [Original Docs Returned Date]6 <> "", [Imaging Date]6 <> ""), 1, 0)

This will allow you to only have the 4 criteria in the AND statement instead of having to specify to look for text and dates for each which would increase your logical statements to 7.

All comments

Amit Wadhwani

Hi Melissa,

you can use - ISTEXT([Your Column Name]) to check if it's a text entry and ISDATE([Your Column Name]) if it's a date.

so what you can do is:

=IF(ISTEXT([Original Docs Returned Date]6), "Do your thing", IF(ISDATE([Original Docs Returned Date]6), "Do your thing", "Else do nothing"))

Paul Newcome

Instead of adding the ISTEXT for each of the columns, you can replace each of the ISDATE criteria to just say if the cell is not blank.

=IF(AND(([Collateral Perfected]6 = 1), [Date Loan Checked Back]6 <> "", [Original Docs Returned Date]6 <> "", [Imaging Date]6 <> ""), 1, 0)

This will allow you to only have the 4 criteria in the AND statement instead of having to specify to look for text and dates for each which would increase your logical statements to 7.

Melissa Shandy

Paul - this worked perfectly. Thank you! I had tried a similar formula but used "IF(ISBLANK" with a 0,1 result instead, but that kept checking cells that didn't contain everything required in the row. Didn't think to use "<>" instead! Thanks again!

Paul Newcome

Happy to help! 👍️

To use the ISBLANK function, you would either specify a result of false or incorporate the NOT function.

ISBLANK(@cell) = false

NOT(ISBLANK(@cell))

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.