How can you derive a date or date range from a week number in Google Sheets? Here’s how you can reverse-engineer the WEEKNUM function to accomplish this.

The WEEKNUM function helps determine the week number for a given date. However, there’s no built-in function to calculate the date or date range from a week number. By “date range,” I mean the period from the start date to the end date of a specific week.

But don’t worry—I’ve got custom formulas for this! All you need is to provide a year and a week number in two cells.

These formulas will convert the given week number into either a single date (start or end of the week) or an entire week’s range of dates.

Example Setup

Take a look at the setup:

• Year is in cell A2.
• Week number is in cell B2.

I’ve created formulas for the following:

• Start date of the week in cell D2.
• End date of the week in cell E2.

For example:

The 7th week of 2018 begins on 11/02/2018 (Sunday) and ends on 17/02/2018 (Saturday).

Additionally, in cell G2, I have a formula that auto-populates all dates within the specified week number. You’ll find these formulas and their usage tips below.

Find the Date from Week Number

Here’s the formula in cell D2 to find the start date of the given week number:

Formula 1: Start Date

=MAX(
   DATE(A2, 1, 1), 
   DATE(A2, 1, 1)-(WEEKDAY(DATE(A2, 1, 1))-1)+(B2-1)*7
)

Formula 2: End Date

To find the end date of the week (cell E2), use:

=MIN(
   DATE(A2, 12, 31), 
   DATE(A2, 1, 1)-(WEEKDAY(DATE(A2, 1, 1))-1)+(B2-1)*7+6
)

Applying Date Formatting

Both formulas return date values. To display them correctly, select the result cell and go to Format > Number > Date.

Formula Explanation

Start Date

1. Year Start Date: =DATE(A2, 1, 1) This formula gives the first day of the year, e.g., 01/01/2018.
2. Weekday Offset: WEEKDAY(DATE(A2, 1, 1))-1 This calculates the weekday number of the year start date. Subtracting 1 adjusts the offset so the week starts on Sunday.
3. Adjust for Week Number: (B2-1)*7 Subtracting 1 from the week number and multiplying by 7 determines how many days to add to the start of the year to get to the desired week.
4. Combine: DATE(A2, 1, 1)-(WEEKDAY(DATE(A2, 1, 1))-1)+(B2-1)*7
   The full formula calculates the start date by combining the adjustments.
5. MAX Function: MAX(DATE(A2, 1, 1), …)
   The MAX function ensures the returned date is not earlier than the year’s start date. This is especially important for week number 1, which might not contain a full Sunday-to-Saturday week.

End Date

To get the end date, simply add 6 days to the start date. However, to ensure the date stays within the same year, we use the MIN function to cap it at 31/12.

Find the Date Range from Week Number

To generate the full week’s dates, use the SEQUENCE function in cell G2.

Formula:

=SEQUENCE(7, 1, 
   MAX(
      DATE(A2, 1, 1), 
      DATE(A2, 1, 1)-(WEEKDAY(DATE(A2, 1, 1))-1)+(B2-1)*7
   )
)

Formula Explanation:

• 7: Generates 7 rows for 7 days.
• 1: Specifies one column.
• MAX(...): The start date serves as the first value in the sequence.

Resources for Further Reading

• Filter Data for a Specific Number of Past Weeks in Google Sheets
• Calculate Week Number Within a Month (1-5) in Google Sheets
• Weekday Name to Weekday Number in Google Sheets
• Reset Week Number in Google Sheets Using Array or Non-Array Formulas
• Sum by Week Number in Excel (Dynamic Array Formula Included)