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

Find more posts tagged with

Formulas

Accepted answers

DKazatsky2

@Carroll Wall

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)

All comments

DKazatsky2

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

Carroll Wall

I am unclear why it would return the error when there are no blanks.

Would I use an IFERROR function here?

DKazatsky2

@Carroll Wall

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)

Carroll Wall

@DKazatsky2

Thank you! This does help. I was unfamiliar with the ISDATE function.

I appreciate the opportunity to learn something new!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.