Pulling a date if 2 cells match, and the date being pulled is within 7 days of a date column.

Looking for assistance on the formula below.

I'm pulling a date from a form sheet. The formula is working correctly up until the , AND function.

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, AND(ISDATE(@cell), @cell <= {2023 Hours and Miles Form - Oil Change She Range 5}, @cell >= [Tuesday Date]@row - 7)), 1)

Find more posts tagged with

Accepted answers

sharkasits

@Dakota What column are you trying to reference with @cell? I think you're missing the range of that column prior to the AND statement. Also, what are you trying to do with the @cell <= {2023 Hours and Miles Form - Oil Change She Range 5} piece? I don't think that will work since it's comparing a value to a range.

See below for the missing range (Range bold, criteria italic)

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change She Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, [Truck #]@row, <<Missing Range>>, AND(ISDATE(@cell), ~~@cell <= {2023 Hours and Miles Form - Oil Change She Range 5}~~, @cell >= [Tuesday Date]@row - 7)), 1)

All comments

sharkasits

See below for the missing range (Range bold, criteria italic)

Dakota

It works... Thank you Sharkasits! The missing range is the same range I am trying to pull. It works... I didn't know where the correct placement was for the range.

=INDEX(COLLECT({2023 Hours and Miles Form - Oil Change She Range 5}, {2023 Hours and Miles Form - Oil Change Range 3}, [Hidden Zone:]@row, {2023 Hours and Miles Form - Oil Change She Range 2}, {2023 Hours and Miles Form - Oil Change She Range 5}, AND(ISDATE(@cell), @cell >= [Tuesday Date]@row - 7)), 1)

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.