Table of Contents
To find the date from a Google Sheets week number, you can use the WEEKDAY function. This function takes two arguments: the week number and the day of the week. For example, WEEKDAY(28, 2) would return the date for the 28th week’s Tuesday. For more information, you can refer to Google Sheets’ documentation on the WEEKDAY function.
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.