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)
Best Answers
-
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"))
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz Software, Exton, PA
https://www.linkedin.com/in/amitinddr/
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"
-
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
-
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"))
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz Software, Exton, PA
https://www.linkedin.com/in/amitinddr/
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"
-
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!
-
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))