invalid data type

andreahydeen

I keep getting "invalid data type" with this formula:

=INDEX(COLLECT({Time Audit Overview %Accurate}, {Time Audit Overview start date}, AND(YEAR({Time Audit Overview start date}) = 2024, MONTH({Time Audit Overview start date}) = 12), {Time Audit Overview restaurant}, [Restaurant Name]@row), 1)

I have checked that my "start date" column is a date column, and the "{Time Audit Overview restaurant}" and "[Restaurant Name]" columns are the same column type.

Anyone have ideas on what else could be giving me the "invalid data type" error?

Paul Newcome

Try this:

=INDEX(COLLECT({Time Audit Overview %Accurate}, {Time Audit Overview start date}, AND(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(MONTH(@cell), 0) = 12), {Time Audit Overview restaurant}, [Restaurant Name]@row), 1)

andreahydeen

PAUL! Thank you so much! That worked! Would you be willing to explain in simple terms how that piece (the date piece specifically) is working just to build my knowledge? I put it into AI to explain it but it just gave me what I already knew.

Paul Newcome

There are two parts to my update.

First is using @cell references. This tells the function to evaluate the previously established range on a cell by cell basis.

Second is the IFERROR. If there is a blank cell in the date column, the YEAR and MONTH functions will throw an error. Using the IFERROR function allows us to replace that error with whatever we want. I use zero because I know that there will never be a year or month zero.