You can use the following basic syntax to get the date from a week number in Excel:
=DATE(B2,1,1)+(A2-1)*7-(WEEKDAY(DATE(B2,1,1)))+1
This particular formula makes the following assumptions:
- Cell A2 contains the week number
- Cell B2 contains the year
- The first day of the week is considered to be Sunday
This formula will return the date of the Sunday for a particular week.
If you’d instead like to return the Monday, replace the 1 at the end of the formula with 2.
If you’d instead like to return the Tuesday, replace the 1 at the end of the formula with 3.
And so on.
The following example shows how to use this formula in practice.
Example: Get Date from Week Number in Excel
Suppose we would like to get the date of the Sunday for week 14 in the year 2023.
We can type the following formula into cell C2 in Excel to calculate this date:
=DATE(B2,1,1)+(A2-1)*7-(WEEKDAY(DATE(B2,1,1)))+1
The following screenshot shows how to use this formula in practice:
The start day of week 14 in 2023 is 4/2/2023.
Note: If the formula returns a number instead, make sure to change the cell format to Date.
If you would instead like to assume that the week starts on a Monday, you can use the following formula to get the date from the week number:
=DATE(B2,1,1)+(A2-1)*7-(WEEKDAY(DATE(B2,1,1)))+2
The following screenshot shows how to use this formula in practice:
The start day of week 14 in 2023 (assuming that weeks start on a Monday) is 4/3/2023.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Convert Date to Day of Week
Excel: How to Add & Subtract Weeks from Dates
Excel: Calculate the Number of Weeks Between Dates
