+ Reply to Thread
Results 1 to 34 of 34

Extract date from year and week

  1. #1
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Extract date from year and week

    I have to extract the monday's date, when choosing year and week in the two dropdowns, could anyone help?



    https://www.excelforum.com/attachmen...1&d=1501135262
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    How are you defining your week numbers?
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    I'm simply choosing? 1 to 53 from the dropdown
    I choose a new week every week - im using it to index/match in a bigger table and extracting data corresponding to the date I'm trying to extract.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    No, sorry, that's not what I meant! Are you using week numbers as Excel defines them, are you simply starting with 01/01 each year as the first day of the week, or what?

  5. #5
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    I might not understand

    My year and week, are simply written numbers 2017 -> 2020 and 1 -> 53

    From the week I choose, I wanna extract the date of monday. I've tried this, but something is of
    =MAX(DATE(C2,1,1),DATE(C2,1,1)-WEEKDAY(DATE(C2,1,1),2)+(C3-1)*7+1)

    I think my problem is that Week 1 doesn't always start on 01-01 every year
    Last edited by HereComesTheBoom; 07-27-2017 at 02:37 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    I don't think you do understand my question, but maybe I haven't phrased it well! Have a read of this and then let me know how YOU are defining your week numbers: https://exceljet.net/excel-functions...eknum-function

    Just seen your edit - yes, that's precisely what I mean!
    Last edited by AliGW; 07-27-2017 at 02:40 AM.

  7. #7
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    I think I get what you mean, but I don't have dates to define the weeks. I'm trying to do it the other way around.

    I choose year and week, and want the date of the monday in that week. It doesn't solve it even if the week is written as =WEEKNUM(1) in the validationslist.

    It still shows that monday 2017 would be 01-01, but monday in that week was 02-01

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    OK - so what do you WANT it to show? The thing is that you will get overlapping with the week numbers, which means that from Monday to Friday most years there will be a mixture of, for example, week 1 and week 2.

  9. #9
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    I want to choose YEAR, and WEEK, and then just extract the date of MONDAY in that given week - I'll sort the overlapping weeks by just +1 in the cells showing tue->fri

  10. #10
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    you may try this
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    Sorry for off-topic interjection:

    Your attachment is not working, and in any case, although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  12. #12
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by teireii View Post
    you may try this
    The link is invalid

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    Quote Originally Posted by HereComesTheBoom View Post
    I want to choose YEAR, and WEEK, and then just extract the date of MONDAY in that given week - I'll sort the overlapping weeks by just +1 in the cells showing tue->fri
    OK, so we can get rid of the week numbers in the column on the left for now - yes?

  14. #14
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by teireii View Post
    you may try this
    This works, but only in 2017 - when choosing 2018, week 1 gets monday as 08-01

  15. #15
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by AliGW View Post
    OK, so we can get rid of the week numbers in the column on the left for now - yes?
    We sure can - I'm only using the numbers in C2 and C3 (they are just showing the C3)

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract date from year and week

    Quote Originally Posted by HereComesTheBoom View Post
    The link is invalid
    I was able to open the file without a problem?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  17. #17
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    still thinking,,,,,
    week 1 of every year may not be on Jan.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by FDibbins View Post
    I was able to open the file without a problem?
    He updated it

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    It's opening now - must have been a glitch. The formulae in it don't work at all, though - it's producing very odd results.

  20. #20
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by teireii View Post
    still thinking,,,,,
    week 1 of every year may not be on Jan.
    First week (week 1) is always placed in jan, there's occasions where 53 is partly in jan though - I know it might not be the FIRST week though, as there can be days from week 53 in jan. But that doesn't explain 2018, where week 1, day 1 is exactly Monday 01-01

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

    Re: Extract date from year and week

    C6=DATE(C$2,1,1)+(A6-1)*7-WEEKDAY(DATE(C$2,1,1))+MATCH(C6,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)
    Try this and copy towards down
    Samba

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

  22. #22
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    can you try this? i tested again, it works.


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by nflsales View Post
    C6=DATE(C$2,1,1)+(A6-1)*7-WEEKDAY(DATE(C$2,1,1))+MATCH(C6,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)
    Try this and copy towards down
    Thanks Siva, but that doesn't give the desired, sadly.

  24. #24
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by teireii View Post
    can you try this? i tested again, it works.


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Yeah it does, when in 2017 - try picking 2018 week 1 - that would be 01-01 - but yours is giving me 08-01 (and it's text, so I cant use it for looking up the date in the table, which is date-formatted.)

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

    Re: Extract date from year and week

    What would be your expected result in your example (attached file)

  26. #26
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Extract date from year and week

    Quote Originally Posted by nflsales View Post
    What would be your expected result in your example (attached file)
    I need mondays DATE, when choosing year and week in C2 and C3

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    Try this:

    =IF(VALUE(TEXT(C$2,"d"))>9,DATE(C$2,1,1)-7,DATE(C$2,1,1))+CHOOSE((WEEKDAY(DATE(C$2,1,1),2)),1,0,-1,-2,-3,-6,-7)+(ROWS(B$6:B6)-1)+(7*C$3)-1
    Last edited by AliGW; 07-27-2017 at 04:33 AM.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    I updated my last post - hope you saw it. The formula does what you want, I think, and the output is in date format.

  29. #29
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    but attached is fine...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the logis is,,
    1) search which day of the week is for the 1 Jan of selected year
    2) the day difference from Monday to previous week of that day
    i.e: 1/1/2020 is Wednesday (3)
    previous week Wednesday is 12/25/2019
    3) find out total day difference
    wednesday (3) - Monday (1) = 2 days difference + 7 days (1 week)
    3) dependent on the weeknum selected to multiply 7 days
    4) reformat it to mm-dd-yyyy
    Attached Files Attached Files
    Last edited by teireii; 07-27-2017 at 05:30 AM.

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    No, it isn't! Change the week number selector to 1 and you are getting a value error.

  31. #31
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    so weird.....but it works just fine in my computer...Capture.JPG

    BTW, is my logis below correct?
    =
    1) search which day of the week is for the 1 Jan of selected year
    2) the day difference from Monday to previous week of that day
    i.e: 1/1/2020 is Wednesday (3)
    previous week Wednesday is 12/25/2019
    3) find out total day difference
    wednesday (3) - Monday (1) = 2 days difference + 7 days (1 week)
    3) dependent on the weeknum selected to multiply 7 days
    4) reformat it to mm-dd-yyyy

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    The logic, as I have interpreted it, is simply that when week 1 is selected, it must include the 1st of the month, which may or may not be a Monday. Once that has been established, you can work out the rest. Try my formula in B6 copied down, and then practise changing the year and week number.

  33. #33
    Registered User
    Join Date
    11-25-2012
    Location
    hk
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Extract date from year and week

    yeah, that's what i understand as well... but what to do with the first if VALUE(TEXT(C$2,"d"))>9??


    just a suggestion, isn't the week number change to a dependent drop down box is better?

  34. #34
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extract date from year and week

    That checks the day that the first of January falls on in the chosen year. I played with this a bit to get the right number, but if it's bigger than 9, it means that the week will need to start seven days earlier: this stops the problem of week one not containing the first. So we don't get 08/01 as the first item on the list - instead, we get 01/01. Hope that makes sense!

    Dependent drop-down - not my call!!! Ask the OP.

+ 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. Week to date, month to date, year to date using SUMIFS
    By jaredf in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-23-2017, 01:51 PM
  2. Replies: 5
    Last Post: 04-11-2017, 09:23 AM
  3. [SOLVED] Extract Year & Week from Product uneven Serial Number
    By anilgopi99i in forum Excel General
    Replies: 19
    Last Post: 04-04-2016, 03:05 PM
  4. [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
  5. calculating month to date, year to date, week to date
    By labogola in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2014, 05:21 AM
  6. Replies: 1
    Last Post: 12-15-2011, 05:32 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