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)

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…
Need Formula for Subtracting Order Quantities from Inventory Quantities
Hi! What formula would I use in Smartsheet to automatically subtract a quantity from one sheet based on what is submitted in another sheet? I have one sheet that is where we will manage the "orders submitted" and on that sheet is different columns for different items that can be ordered and the quantities requested for…
Pulling in data from 1 sheet to another isn't working with any function.
PMO Template - I want to pull in the Project Name Column from the Project Metadata sheet into my custom Attendance Sheet > Project Name Column. I've tried VLOOKUP, INDEX MATCH, JOIN COLLECT, I've changed parenthesis to brackets. I am new to Smartsheets and can't get anything to work. I've watch videos, followed community…