Formula to check box if cell contains Date OR Text

Melissa Shandy
edited 12/15/23 in Formulas and Functions

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)


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!