Invalid date causing error in Formula
I have formulas written based on a date but some of the dates in my sheet contains blanks or text. So in my formula, it is giving me an Invalid Data Type error. Is there a way to determine if the date is valid date in the formula? I can check for blank, but dont know how to check for anything else.
Can you use ISDATE in the formula? this doesnt work.
=COUNTIFS({Region  CE}, ="CA", ISDATE(Date  CE), MONTH(@cell) = [Formula Reference]191)
Comments

Hi Dee
Try this:
=COUNTIFS({Region  CE}, ="CA", [Date  CE]:[Date  CE],ISDATE(@cell), MONTH(@cell) = [Formula Reference]191)
I have assumed the date column is called "Date  CE" and is in the current sheet?
If this formula doesn't work, then you need to Add the Range of the column with the date in then a comma then the ISDATE(@cell) as the criteria.
Hope this helps.
Kind regards
Debbie Sawyer Consultant & Training Manager
debbie.sawyer@smarterbusinessprocesses.com

Hi Debbie, Thank you for the suggestion, but that didnt work either. I have played with these formulas until I am crosseyed and I can not get it to work.

=COUNTIFS({Region  CE}, ="SE", {Date  CE}, <>"", {Date  CE}, <>"closed", {Date  CE}, MONTH(@cell) = [Formula Reference]189)
I am trying to exclude dates that are blank and the ones that have the word closed in them. I can write a formula that counts the ones with blanks and it works. I can write a formula to pull that ones that say closed and that works. But to exclude the blanks and the Closed won't work!!!
Any suggestions.

Hi Dee
Try this!
=COUNTIFS({Region  CE}, ="SE",{Date  CE},NOT(ISBLANK(@cell)),{Date  CE},<>"Closed")
Can you confirm that the Date column is formatted as an actual Date rather than a Text/Number field having data entered as a date (or the word Closed!)?
If it is really a Date column then you should be able to include your Last part too:
=COUNTIFS({Region  CE}, ="SE",{Date  CE},NOT(ISBLANK(@cell)),{Date  CE},<> "Closed",{Date  CE},MONTH(@cell)=[Formula Reference]191)
Good luck!!
Debbie
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!