Formula to check box if cell contains Date OR Text

Melissa Shandy

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)

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.

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