How to Convert Week Number to Date in Excel (2 Suitable Methods)

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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel

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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel

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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel

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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel


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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel

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

DATE and WEEKDAY Functions to Convert Week Number to Date in Excel


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)

MAX and MIN Functions to Convert Week Number to Date

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)

MAX and MIN Functions to Convert Week Number to Date

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)

Convert Week Number to Month in Excel

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 Week Number to Month in Excel


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)

Convert Days to Weeks in Excel


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")

Convert Days to Weeks in Excel

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


<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo