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)

Tags:
· Share on FacebookShare on Twitter

Comments

  • 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

    debbie.sawyer@smarterbusinessprocesses.com

     

    · Share on FacebookShare on Twitter
  • 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. 

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • 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!!

    Debbie

     

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!