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
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!