+ Reply to Thread
Results 1 to 6 of 6

Find where a specific week commencing date falls within a rolling10 week pattern

  1. #1
    Registered User
    Join Date
    02-09-2017
    Location
    UK
    MS-Off Ver
    2019
    Posts
    59

    Find where a specific week commencing date falls within a rolling10 week pattern

    Hi All

    Can anyone help with the following please?

    I would like to be able to enter any week commencing date (starting on a Monday) and be able to find where that week falls within the pattern. The pattern runs for 10 weeks and then reverts back to week one and continues for another 10 weeks and so on. Ideally I want to avoid using vlookup so I do not need to keep a long look up table or have to update the dates on the pattern every 10 weeks. Is this possible?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Find where a specific week commencing date falls within a rolling10 week pattern

    How does 3/20/2017 match with Week 2 since it does not fall within the given ten weeks?
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-09-2017
    Location
    UK
    MS-Off Ver
    2019
    Posts
    59

    Re: Find where a specific week commencing date falls within a rolling10 week pattern

    the pattern would continue, I have listed the initial 10 week pattern with the start date of 02/1/2017(week 1), this pattern will constantly continue into the future, so the 20/03/2017 is week 2 of the second time the pattern repeats and the 29/05/2017 would be week 2 on the third time it repeats etc.

    What I'm asking is their a way to calculate what week the selected date would be without creating a large lookup table?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Find where a specific week commencing date falls within a rolling10 week pattern

    Try

    ="Week " &MOD(WEEKNUM(F3)-1,10)+1

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find where a specific week commencing date falls within a rolling10 week pattern

    If you want the pattern to continue beyond 2017 then try this version

    ="Week "&MOD(INT((F3-DATE(2017,1,2))/7),10)+1
    Audere est facere

  6. #6
    Registered User
    Join Date
    02-09-2017
    Location
    UK
    MS-Off Ver
    2019
    Posts
    59

    Re: Find where a specific week commencing date falls within a rolling10 week pattern

    thank you both, both options give what I wanted especially daddylonglegs option

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Assign a week commencing date to a random date
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-28-2016, 02:48 PM
  2. [SOLVED] Work out week commencing date
    By Bunny Screen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2014, 01:12 PM
  3. Generating week commencing date for 52weeks.
    By sritantry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2012, 01:36 AM
  4. Converting a week number into a week commencing
    By wetbean in forum Excel General
    Replies: 9
    Last Post: 12-21-2011, 06:18 PM
  5. Automatic week commencing date
    By loz786 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2011, 03:57 PM
  6. Week commencing Date
    By amarpabari in forum Excel General
    Replies: 1
    Last Post: 07-06-2011, 04:53 AM
  7. Replies: 3
    Last Post: 07-17-2010, 12:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1