Evaluate a date and return week #

PeggyLang

I have a date input in a worksheet and need to somehow lookup what week # the date falls in.

My weeks begin Monday and end Sunday. Screenshot of 'Date Range' worksheet below.

So if my input date in a separate worksheet is 03/15/24 I want to return 11.

I am using this in a 'LookUp' worksheet. Screenshot of 'LookUp' worksheet below;

Once I am able to return the appropriate 'Week #' I will then use the value in Index/Match formulas to return;

'Tech on Call'
'Supervisor on Call'
'Escalation Supervisor on Call'

The orange shaded cells are manual inputs. The white cells are Index/Match formulas.

Any help/ideas GREATLY appreciated!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

There are quite a few options, but the one I would use is

=MAX(COLLECT({Week #}, {Start Date}, @cell <= [Lookup Value]10))

Paul Newcome

@peggy lang The COLLECT function does just that. It collects cells from the first range from rows that meet the rest of the range/criteria sets.

Once we collect all of the week numbers where the start date (in the table) is less than or equal to your lookup value, we use the MAX function to pull the "largest" week number out of the group of week numbers that we had collected.

All comments

Paul Newcome

There are quite a few options, but the one I would use is

=MAX(COLLECT({Week #}, {Start Date}, @cell <= [Lookup Value]10))

PeggyLang

@Paul Newcome

WOW!!!!!!

THAT WORKED!!! But how?? Can you explain that in 'formula for dummy's' language? This seems like an extremely powerful couple of functions that I as yet know nothing about.

THANK YOU !!!!

Off to my next hurdle in this solution . . . :)

Paul Newcome

@peggy lang The COLLECT function does just that. It collects cells from the first range from rows that meet the rest of the range/criteria sets.

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.