Getting #INVALID DATA TYPE when using the MONTH and YEAR commands

cabbsman

My source sheet is using this formula in the 2025 MAR column:

=COUNTIFS({Christine Test Sheet CLLI Code}, CLLI@row, MONTH({Christine Test Sheet Acq. Cut Date}), 3, YEAR({Christine Test Sheet Acq. Cut Date}, 2025)) on this source sheet:

My destination Sheet that the source sheet refers to i.e., {Christine Test Sheet Acq. Cut Date} maps to the "ACQ. CUT DATE (End)" column (with Date Property) on the destination sheet and looks like as follows:

My expectation is that the formula's MONTH({Christine Test Sheet Acq. Cut Date}) will return the month "3" from the destination sheet's ACQ. CUT DATE (End) column after validating that the CLLI on the source sheet "MICKEY" and CLLI Code on destination sheet "MICKEY" match. Likewise, the YEAR({Christine Test Sheet Acq. Cut Date} would return 2025 in this example.

Instead, I am receiving a #INVALID DATA TYPE Error. Does anyone have some ideas on how to not get this error and get the return values as I was expecting?

Nitish K

Hi

First thing is you cannot include a range in the month formula. It will obviously throw and invalid error.

I will explain you the entire process how this can be done

1) In the destination sheet create a column named as ACQ CUT Month YEAR and extract the month and year from this column ACQ CUT DATE using this formula

=MONTH([ACQ CUT DATE]@row) + "-" + YEAR([ACQ CUT DATE]@row)

U will get the month year respectively

2) Next in the source sheet do something like this

Here what I have done is I have taken the current date by using this formula =TODAY(). U can convert that into column formula. Next Extract the month and year in two separate rows from the current date as shown above using these formulas

=MONTH($[Current Date]@row)

=YEAR($[Current Date]@row)

Then combine them in the Month-Year row using this formula

=[2025 MARCH]3 + "-" + [2025 MARCH]4

Then use the countifs formula to count the occurrence of the CLLI column values

=COUNTIFS({CLLI Code}, CLLI@row, {ACQ CUT Month YEAR}, [2025 MARCH]2)

You will get the counts
MICKY = 2
MOUSE =3

Hope this solution is helpful

Melissa Yamada

Hello @cabbsman

Try this:

=COUNTIFS({Christine Test Sheet CLLI Code}, CLLI@row, {Christine Test Sheet Acq. Cut Date}, IFERROR(MONTH(@cell ),0)= 3, {Christine Test Sheet Acq. Cut Date}, IFERROR(YEAR(@cell ),0)=2025)

Nitish K

Hi

First thing is you cannot include a range in the month formula. It will obviously throw and invalid error.

I will explain you the entire process how this can be done

1) In the destination sheet create a column named as ACQ CUT Month YEAR and extract the month and year from this column ACQ CUT DATE using this formula

=MONTH([ACQ CUT DATE]@row) + "-" + YEAR([ACQ CUT DATE]@row)

U will get the month year respectively

2) Next in the source sheet do something like this

Here what I have done is I have taken the current date by using this formula =TODAY(). U can convert that into column formula. Next Extract the month and year in two separate rows from the current date as shown above using these formulas

=MONTH($[Current Date]@row)

=YEAR($[Current Date]@row)

Then combine them in the Month-Year row using this formula

=[2025 MARCH]3 + "-" + [2025 MARCH]4

Then use the countifs formula to count the occurrence of the CLLI column values

=COUNTIFS({CLLI Code}, CLLI@row, {ACQ CUT Month YEAR}, [2025 MARCH]2)

You will get the counts
MICKY = 2
MOUSE =3

Hope this solution is helpful

Melissa Yamada

Hello @cabbsman

Try this:

=COUNTIFS({Christine Test Sheet CLLI Code}, CLLI@row, {Christine Test Sheet Acq. Cut Date}, IFERROR(MONTH(@cell ),0)= 3, {Christine Test Sheet Acq. Cut Date}, IFERROR(YEAR(@cell ),0)=2025)

cabbsman

Thank you both for your help! Melissa it worked perfectly. The need for @cell keeps throwing me off. Thanks again.

Melissa Yamada

Happy to help! 😊