Evaluate a date and return week #
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!
Best Answers
-
There are quite a few options, but the one I would use is
=MAX(COLLECT({Week #}, {Start Date}, @cell <= [Lookup Value]10))
-
@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.
Answers
-
There are quite a few options, but the one I would use is
=MAX(COLLECT({Week #}, {Start Date}, @cell <= [Lookup Value]10))
-
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 . . . :)
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!