+ Reply to Thread
Results 1 to 17 of 17

Calculate Week Range from Week Number, Month and Year?

  1. #1
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Calculate Week Range from Week Number, Month and Year?

    I'm racking my brain trying to figure out a formula I could use to display the Week Range (Ex. 01/05/16 - 07/05/16) given the (Week Number (1,2,3,4,5 etc..), the month and year. Can anyone help me with this?

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Calculate Week Range from Week Number, Month and Year?

    weeknum should give you want you want

    https://exceljet.net/formula/get-week-number-from-date

    but would also depend on what you classify as first week and also what day of the week you classify as the first day of the week
    ie is the 1st of Jan this year considered week 1 even though it started on Friday or week 53 of last year?
    making 1st week of this year start of 3rd

    ^this is also assuming your week starts on a sunday
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

    Re: Calculate Week Range from Week Number, Month and Year?

    Attached is a workbook with a formula in E2 that takes the week number (within a month), month and year and determines the date range (ending on Saturday) for the week number specified.

    The formula doesn't recognize the number of days in a month, so the week range for the 4th or 5th week in a month may show a date that is past the number of days in a month. To get those specifically defined, I think you would need some VBA.

    Hope this provides some direction,

    Dan
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    Thank you so much for the replies!

    But I dont think I was clear enough with my question.

    So lets say in a cell I have the month, in another cell I have the year and in another cell I have the week number, how can I calculate the date range based on this information?

    When I say weeknumber, I mean specific to the month, so week 1 is the first week of the month listed above, week 2 is the second week of the month listed above, etc... not the week number for the year.

    Does this make sense? :x

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,896

    Re: Calculate Week Range from Week Number, Month and Year?

    pls attach the sample file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    I dont have a sample file. I dont even know how to start the code

  7. #7
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Calculate Week Range from Week Number, Month and Year?

    Put week number in cell B1, Month in cell B2, and year in B3.

    For result use this formula-
    Please Login or Register  to view this content.
    Check attached for example
    excelforum-163.xlsx
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  8. #8
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    Thats perfect!!!

    How do I make it though so that the first week of the month is the week where the first of the month is in? (So the first of the month, that sun-sat week is the first of the week)

    So for example, the first week of June 2016 would display May 29th to June 4th (sun-sat)?

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,896

    Re: Calculate Week Range from Week Number, Month and Year?

    Please Login or Register  to view this content.
    try above formula in "sourabhg98" attached file

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,896

    Re: Calculate Week Range from Week Number, Month and Year?

    Please Login or Register  to view this content.
    try above formula in "sourabhg98" attached file

  11. #11
    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,887

    Re: Calculate Week Range from Week Number, Month and Year?

    See attached.

    Based on 1st of month, column G gives the Sunday of week containing first of month (Week 1).

    =F2-WEEKDAY(F2,1)+1

    For Week 1

    in H2

    =TEXT($G2+(H$1-1)*7,"dd/mm/yy") &"-"& TEXT($G2+(H$1-1)*7+6,"dd/mm/yy")

    Copy across and down
    Attached Files Attached Files
    Last edited by JohnTopley; 05-06-2016 at 05:12 AM.

  12. #12
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    Thank you so much, but Im looking for the exact set up that "sourabhg98" did, and I put in 'nflsales' edit, but it didnt work fully. For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april

  13. #13
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    5,884

    Re: Calculate Week Range from Week Number, Month and Year?

    See attached...
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    Yes!!! Exactly what Im looking for!! :D
    Last question, how do I make it so that the month cell equals whatever number value is in another cell (J3)? It messes up when I put =J3 for example.

  15. #15
    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,887

    Re: Calculate Week Range from Week Number, Month and Year?

    If you are referring to my table ..

    For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april

    Week 1 of May is given as 01/05/2016-07/05/2016.

    See attached which uses the table I created.

    What do you want (as is the usual case) when Week 5 (or 6) for month n is Week 1 of month n+1?
    Attached Files Attached Files
    Last edited by JohnTopley; 05-07-2016 at 06:08 AM.

  16. #16
    Registered User
    Join Date
    05-05-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    26

    Re: Calculate Week Range from Week Number, Month and Year?

    Thank you so so much for all of the help!!!! Amazing experience on here!! :D Thanks Guys!, I got it figured out based on your work :D

  17. #17
    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,887

    Re: Calculate Week Range from Week Number, Month and Year?

    Could you please mark the thread as solved ("Thread Tools" at top of first post).

+ 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. Calculate week number for Fiscal Year Dates
    By katie_10042 in forum Excel General
    Replies: 16
    Last Post: 02-14-2017, 05:58 AM
  2. VBA to calculate AVERAGEIF and SUMIF for current week/month/year
    By lostest in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2016, 07:35 AM
  3. [SOLVED] Get the date of saturday in a week from a week number / year
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2015, 06:56 PM
  4. Calculate Start & End Date & Month for Each Week In Fiscal Year
    By sarndt01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2014, 02:14 PM
  5. Calculate week number for Fiscal Year Dates
    By Ginu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 03:10 PM
  6. Calculate week number in month
    By PRodgers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2010, 11:38 AM
  7. [SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week
    By amy in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-23-2005, 11:42 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1