Method 1 – Using the DATE and WEEKDAY Functions to Convert Week Number to Date
There are no straightforward functions to convert the week number to date in Excel. That’s why we have to create a formula using the DATE and the WEEKDAY functions. Take a look at the following screenshot:
The example is based on the ISO week system as it’s the most popular European week calculation system. In this system the weekday begins on Monday and the week with the first Thursday of the year is regarded as week 1.
To get the start date enter the following formula in Cell E5:
=DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7
Note: When you enter the provided formula it will return a serial number format. You’ll have to convert the serial number to a date. Once that’s done you can proceed with the following steps.
Breakdown of the Formula
DATE(B5, 1, -2) – WEEKDAY(DATE(B5, 1, 3)): This returns the last Monday of the previous year.
C5 * 7: By adding the number of weeks multiplied by 7 we get the Monday of the given year.
In the ISO week calculation system, the week containing the first Thursday is considered as week 1. As such, the first Monday always falls in between the dates December 29th and January 4th. To discover the exact date you have to know what the Monday before January 5 is.
If your goal is to find the first Monday directly before January 5th of the year in B5, you should use the following DATE(year, month, day) functions:
=DATE(B5,1,5) - WEEKDAY(DATE(B5,1,3))
If you want to find the last Monday of the year before that instead, you must subtract seven days from January 5. As such, we use -2 in the first DATE function below:
=DATE(B5,1,-2) - WEEKDAY(DATE(B5,1,3))
You can now easily find the last date of the week by using the following formula:
=E5+6
You can also use the previous formula and add six instead to get the end of the week.
=DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7+6
1.1 Mon-Sun Week (Starts from Jan 1)
The previous method is based on the ISO week calculation system. If you don’t want to follow this system you can use the following Excel formula.
If your week 1 starts from January 1st and Monday is the start of the week, the below formulas will help change the week number into a date.
We are using the WEEKDAY function and the DATE function again.
Enter the following formula in Cell E5 and drag the fill handle icon down:
=DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7
To get the End Dates, enter the following formula in Cell F5 and drag the fill handle icon down:
=DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7+6
1.2 Sun-Sat Week (Starts from Jan 1)
If your week starts on Sunday, you can use a similar Excel formula to get to the start date and end date.
To get the start dates, enter the following formula in Cell E5 and drag the fill handle icon down:
=DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),1) + (C5-1)*7 + 1
To get the end dates, enter the following formula in Cell F5 and drag the fill handle icon down:
=DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),1) + (C5-1)*7
Method 2 – MAX and MIN Functions to Convert Week Number to Date
To convert the week number to date in Excel, you can use the MIN function and the MAX function with the same formula.
2.1 Always Counting Mon-Sun Week (Starts from Jan 1)
You can wrap the Excel formula in the MAX function and the MIN function to always start counting from January 1.
To get the start dates, enter the following formula in Cell E5 and drag the fill handle icon down:
=MAX(DATE(B5,1,1), DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),2) + (C5-1)*7 + 1)
To get the end dates, you have to use the MIN function and make a slight change in the formula. Enter the following formula in Cell F5 and drag the fill handle icon down:
=MIN(DATE(B5+1,1,0), DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),2) + C5*7)
2.2 Always Counting Sun-Sat Week (Starts from Jan 1)
If your week 1 starts with Sunday, you have to make a slight change in the above formulas.
To get the start date, enter the following formula:
=MAX(DATE(B5,1,1), DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),1) + (C5-1)*7 + 1)
For the end dates, use the following formula to convert the week number to date:
=MAX(DATE(B5,1,1), DATE(B5,1,1) - WEEKDAY(DATE(B5,1,1),1) + (C5-1)*7 )
Read More: How to Convert Timestamp to Date in Excel
Convert Week Number to Month in Excel
To convert the week number to a month we are also using the WEEKDAY function and the DATE function. This time, we are wrapping these up in the MONTH function.
Take a look at the following dataset:
Enter the following formula in Cell E5 and drag the fill handle icon down:
=MONTH(DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7)
As you can see, we got the month number. But if you want the month name, try the following formula:
=CHOOSE(MONTH(DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7),"January","February","March","April", "May", "June", "July", "August", "September", "October", "November", "December")
The above formula contains the CHOOSE function.
Convert a Date to Weeks in Excel
1. Convert a Date to a Week Number in Excel
Previously, we’ve used formulas to convert a week number to date. You can also convert days to the week number. To do this, we will use the WEEKNUM function.
The WEEKNUM function is used to calculate the week number of a date.
The Generic Syntax:
=WEEKNUM(serial_number, [returns_type])
The serial number indicates the date. We know Excel also recognizes dates as serial numbers and that it returns type indicates from which day our week will begin.
Take a look at the following dataset:
We’ll convert the listed dates to the week numbers they fall in.
Click on Cell D5. Enter the following formula and drag the fill handle icon down:
=WEEKNUM(B5)
2. Convert Number of Days to Weeks in Excel
Take a look at the following dataset:
You can see the number of days took to complete a project. This might not be detailed enough for you so we can convert the amount of days to the amount of weeks. We’ll accomplish this using the following formula:
Click on Cell D5 and enter the following formula. Then drag the fill handle icon down:
=INT(C5/7)&IF(INT(C5/7)=1," week"," weeks") & " and " & (C5-INT(C5/7)*7) & IF((C5-INT(C5/7)*7)=1," day"," days")
As you can see, our Excel formula successfully converted the amounts of days to the amount of weeks and days.
Breakdown of the Formula
INT(C5/7): This returns the number of weeks.
C5-INT(C5/7)*7: This returns the number of days that are not sufficient for the weeks (less than 7 days).
We added the IF function to properly display singular (“week”) or plural (“weeks”).
The same applies to days, if you have 1 day, it will add “day”. Otherwise, it will add “Days”. It helps make the output grammatically correct.
Download Practice Workbook
Related Articles
- How to Convert 13 Digit Timestamp to Date Time in Excel
- How to Convert Active Directory Timestamp to Date in Excel
- How to Convert SAP Timestamp to Date in Excel
- How to Convert Unix Timestamp to Date in Excel
<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I tried this for a report spanning weekly data from 2011-2024.
Going back more than 4 years (before 2020) the first formula gets week one off by a week later, .
Don’t this formula not consider leap years or something? It would offset 1/7th of a week only though I’d figure.
I can’t figure it out myself.
In my region week one is always the first week of the year with more than four days, meaning beginning thursday latest, as your example seems to include.
Hello Jim,
Thanks for the update! If you’re still encountering issues with Week 1, consider using the ISOWEEKNUM function to get the correct ISO week number:
=ISOWEEKNUM(A1)
This function aligns with your region’s definition of Week 1.
To account for ISO week numbering where Week 1 starts on the first week with at least four days, you can use the following formula:
=DATE(B5,1,4) – WEEKDAY(DATE(B5,1,4), 2) + (A5-1)*7
This should help correct the offset.
N.B: Leap years shouldn’t shift by a full week, but they could affect the day counts.
Regards
ExcelDemy
Adjusted to
=DATE(B5,1,-3) – WEEKDAY(DATE(B5,1,2))
… then I get it to work back to 2016 but then it fails to recognise Week1 as beginning january 4th and says december 28th instead…
Thanks for the great article anyway. I wouldnät have got this far without you!
Hello Jim,
Thanks for the update! If you’re still encountering issues with Week 1, consider using the ISOWEEKNUM function to get the correct ISO week number:
=ISOWEEKNUM(A1)
This function aligns with your region’s definition of Week 1.
To account for ISO week numbering where Week 1 starts on the first week with at least four days, you can use the following formula:
=DATE(B5,1,4) – WEEKDAY(DATE(B5,1,4), 2) + (A5-1)*7
This should help correct the offset.
N.B: Leap years shouldn’t shift by a full week, but they could affect the day counts.
Regards
ExcelDemy