Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to convert week number to date or vice versa in Excel?

Is there a way to get the week number from a given date or extract the date range from a specific week number and year in Excel? To solve this task, the following formulas may do you a favor.

Convert week number to date with formulas

Convert date to week number with formulas

One click to convert multiple non-standard formatting dates/numbers/text to normal dates in Excel

Kutools for Excel's Convert to Date utility can help you easily identify and convert non-standard dates or numbers (yyyymmdd) or text to normal dates with only one click in Excel. Full Feature Free Trial 60-day!
ad convert to date 1

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial


arrow blue right bubble Convert week number to date with formulas

Supposing I have a random year and week number which are 2015 and 15 in a worksheet as following screenshot shown, and now I want to find out the specific dates from Monday to Sunday by this given week number.

To calculate the date range by the specific week number, you can use the following formulas:

1. Select a blank cell you will return the start date (in our case we select the Cell B5), enter the formula: =MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1), and press the Enter key. See screenshot below:

2. Select another blank cell you will return the end date (in our case we select the Cell B6), enter =MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7), and press the Enter key. See screenshot below:

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Note: In both formulas above, B1 contains a year and B2 is a specified week number, you can change the arguments to your need).

3. As you see, both formulas return numbers instead of dates. Keep selecting both calculating results, and click Home > Number Format box > Short Date to change the numbers to dates. See screenshot below:


arrow blue right bubble Convert date to week number with formulas

On the other hand, you can also apply the WEEKNUM function to convert a date to corresponding week number.

1. Select a blank cell you will return the week number, enter this formula: =WEEKNUM(B1,1), and press the Enter key. See screenshot:

Notes:

(1) In above formula, B1 contains the date that you want to use.

(2) If you need to return the week number from a date which begins in Monday, please apply this formula: =WEEKNUM(B1,2).


arrow blue right bubbleRelated articles:

How to count the number of specific weekdays between two dates in Excel?

How to add / subtract days / months / years to date in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    jtaylorg91 · 1 months ago
    Thank YOU!!!!
  • To post as a guest, your comment is unpublished.
    geoffsmile · 1 years ago
    Thank you very much!


    =CONCATENATE("Inclusive Dates: ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"MMMM")," ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"DD"),", ",TEXT(MAX(DATE(TEXT(TODAY(),"yyyy"),1,1),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+(WEEKNUM(TODAY())-1)*7+1),"YYYY")," - ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"MMMM")," ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"DD"),", ",TEXT(MIN(DATE(TEXT(TODAY(),"yyyy")+1,1,0),DATE(TEXT(TODAY(),"yyyy"),1,1)-WEEKDAY(DATE(TEXT(TODAY(),"yyyy"),1,1),2)+WEEKNUM(TODAY())*7),"YYYY"))
  • To post as a guest, your comment is unpublished.
    seld · 2 years ago
    Date(2017;1;7 * weeknumer - 5)

    5 monday, 4 tuesday... :)
    • To post as a guest, your comment is unpublished.
      Katy · 1 years ago
      Or to make in not specific to the year 2017...
      =DATE(YEAR,1,7 * WEEKNUM - WEEKDAY(DATE(YEAR,1,7) - 2))

      2 monday, 3 tuesday,...
  • To post as a guest, your comment is unpublished.
    Lynnwood · 2 years ago
    I'm sorry folks, but I could neither understand nor make work any of the formulas above so I finally figured out the following solution:
    DATE(B1,1,1)+($A4×7)−(6−(7−WEEKDAY(DATE(B1,1,1),first-day)))

    The first part "DATE(B1,1,1)+(B2×7)" simply takes January 1 of the year and adds the number of weeks.

    The next part calculates how many days to subtract from the WEEKDAY of January 1 to get the first day of the week. This is what I finally figured out:
    6−(7−WEEKDAY(DATE(B1,1,1),first-day))
    If, for example, January 1 falls on a Sunday (day 7), then this formula become "6-(7-7)" or simply 6 - which is the number of days you need to subtract to get Monday of that week. Try other days.

    Finally, if you want to find any other day of the week, just add the WEEKDAY number minus 1 to this result. So the last day of the week (Sunday) is:
    DATE(B1,1,1)+($A4×7)−(6−(7−WEEKDAY(DATE(B1,1,1),first-day)))+(7−1)
    Which can be simplified to:
    DATE(B1,1,1)+($A4×7)−(12−(7−WEEKDAY(DATE(B1,1,1),first-day)))
    Wednesday would be:
    DATE(B1,1,1)+($A4×7)−(8−(7−WEEKDAY(DATE(B1,1,1),first-day)))
    etc.
    I hope this helps someone else who needs this both with a workable solution along with a bit of understanding of how it was arrived at!
  • To post as a guest, your comment is unpublished.
    TJ · 2 years ago
    It's a great formulas and ideal! However, it's important to point out that Excel default system week number counts from Sunday to Saturday. Adjustment is needed if the week number is extracted from Excel Weeknumber formulas.