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!

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • PeggyLang
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!