Invalid date causing error in Formula

Dee Barnard

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)

Debbie Sawyer

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

 

Dee Barnard

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

Dee Barnard

 

=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.

Debbie Sawyer

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