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 non-date 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.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!