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
-
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 SawyerConsultant & Training Manager
-
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)=[FormulaReference]191)
Good luck!!
Debbie
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/47396/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
