invalid data type

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?

Find more posts tagged with

Accepted answers

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)

All comments

Paul Newcome

Try this:

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.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.