Invalid date causing error in Formula

Dee Barnard
Dee Barnard ✭✭✭
edited 12/09/19 in Formulas and Functions

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


  • Dee Barnard
    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
    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
    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!!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!