How to count dates in a column? INVALID DATATYPE error
I want to count how many dates in a column called "Completed" occur in the month of January.
Using this formula
=COUNTIF([Completed]:[Completed], MONTH(@cell) = 1)
returns INVALID DATATYPE whether I use it in a cell on the sheet or in a sheet summary field or on a separate metric sheet with the formula referencing the Completed column in the data sheet.
The explanation of the error is as follows: The formula contains or references an incompatible data type, such as =INT("Hello")
The column I reference is set up as a date column and the column where the formula is is a text/# column. I am unclear where/why the problem is occurring. I feel like it must be something obvious/simple but I am stumped.
I appreciate your insights, Smartsheet Community!
Thanks,
Carroll
Best Answer

IFERROR isn't what you want in this situation. Try the following, it should exclude any rows that don't have a valid date, which includes blanks.
=COUNTIFS(Completed:Completed, ISDATE(@cell), Completed:Completed, MONTH(@cell) = 1)
Answers

Hi @Carroll Wall,
The formula you have posted is correct, there is most likely a row(s) that has a nondate value or is blank, which is throwing the error.
Hope this helps,
Dave

I am unclear why it would return the error when there are no blanks.
Would I use an IFERROR function here?

IFERROR isn't what you want in this situation. Try the following, it should exclude any rows that don't have a valid date, which includes blanks.
=COUNTIFS(Completed:Completed, ISDATE(@cell), Completed:Completed, MONTH(@cell) = 1)

Thank you! This does help. I was unfamiliar with the ISDATE function.
I appreciate the opportunity to learn something new!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 405 Global Discussions
 216 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!