Assist with Date Helper Column Formula

John Stanik

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

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