You can use the following basic syntax to get the date from a week number in Google Sheets:
=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 Google Sheets
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 Google Sheets 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.
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 Google Sheets:
How to Group Data by Week in Google Sheets
How to Add & Subtract Weeks from Date in Google Sheets
How to Extract Year from Date in Google Sheets
