+ Reply to Thread
Results 1 to 18 of 18

How to derive a week number from a Retail Week Calendar

Hybrid View

  1. 03-01-2020, 12:38 PM #1
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    How to derive a week number from a Retail Week Calendar

    Hi All,

    Hoping you can help with something that I just can't seem to work out..

    I need to automate reporting and work in Retail; our financial year begins on 29/12/2019 and weeks are reported Sunday - Saturday.

    I need to pass a date and for the function to return the correct week.

    Could anyone help me?

    Thanks,
    Michael
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-01-2020, 12:42 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Welcome to the forum.

    Please be more specific about your Excel version in your profile - is it Excel 365 that you have?
    Last edited by AliGW; 03-01-2020 at 12:49 PM. Reason: Typo corrected.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-01-2020, 12:45 PM #3
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Hi,

    Yes; I use Excel 365 in the Office and at home (where I am now) I'm using Excel for Mac version 16.16.19.

    Hope that's a little clearer.

    Thanks,
    Michael
    Reply With QuoteRegister To Reply
  4. 03-01-2020, 12:44 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Is the dates table for our benefit, or can if be used in a formula?
    Reply With QuoteRegister To Reply
  5. 03-01-2020, 12:46 PM #5
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    The dates table can be used in a formula..

    Ideally i wouldn't need to use the dates table and I could plug in a 'start date' and work it out dynamically.

    If there's no other option, then we could use the dates table.

    Thanks,
    Michael
    Reply With QuoteRegister To Reply
  6. 03-01-2020, 12:49 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Please update your profile: give both versions if one is Windows and the other Mac.
    Reply With QuoteRegister To Reply
  7. 03-01-2020, 12:50 PM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Why do you have 29/01/2019 as the first date? It isn't part of the current year, so how do you want that to work?
    Reply With QuoteRegister To Reply
  8. 03-01-2020, 12:51 PM #8
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    That's when our trading year starts...

    It works off a retail calendar vs a standard ISO calendar.. which is what is making this a little difficult!
    Reply With QuoteRegister To Reply
  9. 03-01-2020, 12:51 PM #9
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Profile updated
    Reply With QuoteRegister To Reply
  10. 03-01-2020, 12:51 PM #10
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,463

    Re: How to derive a week number from a Retail Week Calendar

    Does this formula work correctly for you...

    =MATCH(G4,C$4:C$56,1)
    Reply With QuoteRegister To Reply
  11. 03-01-2020, 12:53 PM #11
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Thanks rick; it doesn't unfortunately; it just gives me an N/a
    Reply With QuoteRegister To Reply
  12. 03-01-2020, 12:56 PM #12
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,463

    Re: How to derive a week number from a Retail Week Calendar

    Quote Originally Posted by moconn View Post
    Thanks rick; it doesn't unfortunately; it just gives me an N/a
    Do you get that error for all of the dates or just the first one? I ask because you first date is listed as January 29, 2019 instead of December 29, 2019. I had that error for the first one also until I changed it to the correct date. Just so you know, the formula I posted works fine for me here in my US Locale dates.
    Last edited by Rick Rothstein; 03-01-2020 at 12:58 PM.
    Reply With QuoteRegister To Reply
  13. 03-01-2020, 01:02 PM #13
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Hi Rick,

    That's it!! Once i changed the first one to 29/12/19 it worked perfectly for all cases! Thank you so much

    Is there any way to do it without the table?
    Reply With QuoteRegister To Reply
  14. 03-01-2020, 12:57 PM #14
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Does this work for you?

    =ISOWEEKNUM(G4+1)
    Reply With QuoteRegister To Reply
  15. 03-01-2020, 01:03 PM #15
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    Did you try my suggestion?
    Reply With QuoteRegister To Reply
  16. 03-01-2020, 01:45 PM #16
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Yes, I've just tried it and it also worked a treat - you guys are genius's!

    Thanks SO much
    Reply With QuoteRegister To Reply
  17. 03-01-2020, 01:54 PM #17
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,266

    Re: How to derive a week number from a Retail Week Calendar

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  18. 03-01-2020, 02:10 PM #18
    moconn
    moconn is offline
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 365 (PC) and Mac 16.16.19
    Posts
    10

    Re: How to derive a week number from a Retail Week Calendar

    Thanks; I didn't know that!

    Thread marked solved and reputation updated for both answers - thanks again guys
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Derive the the number of days based on the given date for the week
    By Excel God in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-10-2018, 04:34 AM
  2. Calculation of Week Number & Week Ending Date formula from a Year Cell
    By Neilesh Kumar in forum Excel General
    Replies: 5
    Last Post: 04-11-2017, 09:23 AM
  3. offset week number for a 4-4-5 calendar
    By jjjjjjjjunit in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-22-2017, 09:18 AM
  4. Returning the week number for a week that starts on Saturday in the Australian Calender
    By jshaw82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-02-2013, 06:48 AM
  5. Derive day of the week from date
    By jaZZerkill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2012, 05:13 AM
  6. Allocating expenses using a 4 week/5 Week accounting calendar
    By AFIOF719 in forum Excel General
    Replies: 0
    Last Post: 01-19-2012, 06:11 PM
  7. date --> calendar week number
    By jmwismer in forum Excel General
    Replies: 8
    Last Post: 10-03-2009, 10:39 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1