Table of Contents
Excel has a built-in function called WEEKDAY which allows you to easily obtain the date of a given week number. To use the function, you must input the week number, followed by the day of the week you would like to retrieve, and the type of calendar system you are using (1 for a system that starts on Sunday or 2 for a system that starts on Monday). The WEEKDAY function will return the corresponding date for the given week number. This is a useful tool for quickly retrieving dates from a specific week, without having to manually calculate the date from the week number.
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.
=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.