A.N.M. Mohaimen Shanto
In Microsoft Excel, working with week and date is one of the crucial tasks. You will find yourself in a lot of situations where you will have to find a week number from a date or date from a week number. So, it is ideal to know every detail about these to perform these quite easily. In this tutorial, you will learn to convert the week number to date in Excel with suitable examples and proper illustrations. So, stay with us.
Download Practice Workbook
2 Ways to Convert Week Number to Date in Excel
In the upcoming sections, I am going to show you two essential formulas that you can use in your worksheet. It will help you to convert the week number to date in Excel. I recommend you learn and apply all these methods.
Now, let’s clarify it first. Both of the formulas will contain the DATE function and the WEEKDAY function. These functions will help you create a formula.
Let’s get into it.
1. Using DATE and WEEKDAY Functions to Convert Week Number to Date
Now, there are no straightforward functions to convert the week number to date in Excel. That’s why we are creating a formula to do that. We are using the DATE function and the WEEKDAY function. Take a look at the following screenshot:
This example is based on the ISO week system. In this system, the weekday begins with Monday, and the week retaining the first Thursday of the year is regarded as week 1. It is popular as the European week calculation system.
Now, to get the start date, type the following formula in Cell E5:
=DATE(B5, 1, -2) - WEEKDAY(DATE(B5, 1, 3)) + C5 * 7
Note: If you type the formula, it will return a serial number format. So, before you do anything, convert the Serial Number to Date. After changing the format, perform these.
🔎 Breakdown of the Formula
DATE(B5, 1, -2) – WEEKDAY(DATE(B5, 1, 3)): It returns the last Monday of the previous year.
C5 * 7: We added the number of weeks that is multiplied by 7 to get the Monday of the given year.
In the ISO week calculation system, the week containing the first Thursday is considered as week 1. Therefore, you can find the first Monday between December 29 and January 4. So, to discover that date, you have to see the Monday instantly before January 5.
If your goal is to find Monday directly before January 5 of the year in B5, use the following DATE(year, month, day) functions:
=DATE(B5,1,5) - WEEKDAY(DATE(B5,1,3))
Now, we didn’t want to find the first Monday of this year, but instead the final Monday of the earlier year. So, we subtracted seven days from January 5. After that, we got -2 in the first DATE function:
=DATE(B5,1,-2) - WEEKDAY(DATE(B5,1,3))
Now, you can easily find the last date of the week by using the following formula:
=E5+6
Similarly, you could use the previous formula and add six with that 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)
Now, the previous method we just showed is on the ISO week calculation system. Here, the weekday date starts from Thursday as week 1. If you work in a zone that doesn’t follow this system, use the following Excel formula.
If your week 1 starts from January 1 and Monday is the week to start, these formulas will help you get that.
We are using the WEEKDAY function and the DATE function.
To get the start dates, type 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, type 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 with Sunday, you can use a similar Excel formula to return the start date and end date.
To get the start dates, type 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, type 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
As you can see, we are successful in converting the week number to date in Excel.
Related Content: How to Convert General Format to Date in Excel (7 Methods)
Similar Readings
- Text Won’t Convert to Date in Excel (4 Problems & Solutions)
- How to Convert Unix Timestamp to Date in Excel (3 Methods)
- Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
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.
We saw in the previous formulas, they return Monday or Sunday of week 1 even if it falls within the same year that you give or the earlier year. The start date formula always returns January 1 as the start date of week 1 . Automatically, the end date formula consistently returns December 31 as the end date of the last week in the year, regardless of the day of the week.
2.1 Always Counting Mon-Sun Week (Starts from Jan 1)
Now, your week 1 starts with January 1 and the weekday is Monday. 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, type 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. Now, type 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.1 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, type 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 )
As you can see, the formula will convert the week number to date in Excel.
Read More: Excel VBA to Convert Date and Time to Date Only
Convert Week Number to Month in Excel
Now, to convert a week number to a month you can also use the previous Excel formulas in a different way.
Here, we are also using the WEEKDAY function and the DATE function to calculate. But, this time, we are wrapping these up in the MONTH function.
Take a look at the following dataset:
Here, we have the week numbers for the year 2022. We will get the month using the formula.
Now, type 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")
Here, we used the CHOOSE function to convert the month number into month names.
As you can see, we are successful in converting a week number to a month in Excel.
Read More: How to Convert Number to Date in Excel (6 Easy Ways)
Convert Days to Weeks in Excel
1. Convert a Date to a Week Number in Excel
Now, previously we used the Excel formulas to convert a week number to date. You can do vice-versa. That means you can convert days to the week number. To perform that, we will use the Excel WEEKNUM function.
The WEEKNUM function is used to calculate the week number of a date.
The Generic Syntax:
=WEEKNUM(serial_number, [returns_type])
Here, the serial number indicates the date. Now, we know Excel also recognizes dates as serial numbers. And returns type indicates from which day our week will begin.
Take a look at the following dataset:
Here, we have some days and we will convert them to the week number.
Now, click on Cell D5. Then type the following formula in and drag the fill handle icon down:
=WEEKNUM(B5)
After that, the function will successfully convert the days to weeks in Excel.
2. Convert Number of Days to Weeks in Excel
Take a look at the following dataset:
Here, you can see the number of days took to complete a project. Now, the number of days is not enough. We have to convert them to weeks and days. So, we will use an Excel formula to solve it.
Our Excel formula will contain the INT function and the IF function.
Now, click on Cell D5. Then type the following formula in and 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 days to weeks.
🔎 Breakdown of the Formula
INT(C5/7): It returns the number of weeks.
C5-INT(C5/7)*7: It returns the number of days that are not sufficient for the weeks (less than 7 days).
We added the IF function to handle the “week” or “weeks” issue. If you have one week, it will add “week” after the 1.
The same applies to days, if you have 1 day, it will add “day”. Otherwise, it will add “Days”. It makes the output grammatically correct.
Read More: How to Convert Text to Date in Excel (10 ways)
💬 Things to Remember
✎ The formula basically returns the dates in serial number format. So, change the format to Dates from the Excel ribbon.
✎ In ISO week date system, the week begins with Monday, and the week including the first Thursday of the year is assumed week 1.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to convert the week number to a date in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
