Assist with Date Helper Column Formula

Hi,

I have a sheet with a Date helper column that is being generated from a Primary column with a MM/DD/YYYY text format. I can get the helper column (set as a date type) to show up as MM/DD/YY without issue using this column formula:

=DATE(VALUE(20 + RIGHT(Primary@row, 2)), VALUE(LEFT(Primary@row, 2)), VALUE(MID(Primary@row, FIND("/", Primary@row) + 1, 2)))

I'm now trying to get a count of the dates in the helper column that fall into particular quarters.

Here's the attempted formula for quarter 1 of 2022 (January to March):

=COUNTIFS(Helper:Helper, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 3, 31)))

but I keep getting a #INVALID DATA TYPE error.

Any suggestions?

Thanks,

John

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

Seems like your Helper column values may not be dates after all. You can test that with this in a text/number column:

=IF(ISDATE(Helper@row), "yes", "no")

John Stanik

Hi Jeff,

Thanks for the response... it comes back as "Yes"

So it is a date-type cell/column then, correct?

The strange thing is, when I click on the cells... the calendar does not appear like it does in other Date-type cells.

John

katie.mcelroy

I'm having this same issue. Did you find a solution?

Genevieve P.

Hi @katie.mcelroy

Can you post a screen capture of your sheet (but block out sensitive data)? It would also be helpful to see what formula you're currently using.

Cheers,

Genevieve

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.