What about getting a date or date range from a week number in Google Sheets? Here is the reverse of the WEEKNUM function.

With the help of the function WEEKNUM, you can get the week number of a given date.

Must Check: The ultimate date functions guide in Google Sheets.

There is no function to populate the date or date range from a week number. But I have the custom formulas to do this.

What you want to do is to provide a year and a week number in two cells.

The formula can convert the provided week number to a date or date range that falls in the given week.

See the above screenshot for the year in cell A2 and the week number in cell B2.

In cell D2, I have one formula that returns the week start date, and in cell E2, another one for the week ending date.

Week number 7 in 2018 starts on 11/02/2018 (Sunday) and ends on 17/02/2018 (Saturday).

In cell G2, I’ve one more formula that auto-populates the dates of the given week number. You can find those formulas and usage tips below.

How to Find the Date or Date Range from Week Number in Google Sheets

Here is my above-said formula in cell D2, which can help you to get the start date from a given week number.

Formula 1:

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

Below is the second formula in cell E2 that can use to find the date from the week number but the last date in the week number period.

Formula 2:

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

Both of the above formulas (bolded part) are almost the same (I’ll explain the MAX/MIN part later).

To get the end date, add # 6, i.e., equal to 6 days, to the first formula result. That’s my second formula.

One more formula is there in cell G2. It’s an array formula. Before going to that, here is the above formula logic.

Some of you may want to learn how the above formula 1 works.

Formula Logic

We have the year in cell A1.

With that year, we can get the very first date in that year using the below formula. That’s the first thing you should do.

=DATE(A2,1,1)

This formula will return the year 01/01/2018 since the year is 2018 in cell A1.

Then I have the week number in B1, which is # 7.

Some of you may think that a formula like the below one can find the date from the week number in Google Sheets.

=DATE(A2,1,1)+(7*7)

But it may not work.

In this formula, the first number 7 in the multiplication factor represents the week number 7, and the second number 7 represents the number of days in a week.

This formula would return the date 19/02/2018, which is wrong! Why?

It would probably return the last date in week 7, not the starting date. Then what about the below formula?

=DATE(A2,1,1)+(6*7)

It is almost perfect. Still not the correct one. I will tell you why?

You may check my date-related function tutorial below, in which I’ve marked the week number in January on a calendar screenshot.

Tips: How to Find the Current Month’s Week Number In Google Sheets

What’s that?

Usually, one whole week is from Sunday to Saturday.

But for the first week in a year, it may or may not be from Sunday to Saturday.

Note:- To get Monday to Sunday, use the “type” argument within the WEEKDAY(date, type) in my formula above.

The week starts on the 1st day of the year, irrespective of Sunday, Monday, or any other day.

So in the first week, the number of days may or may not be 7.

That causes the problem in the above calculations.

See this 2018 Calendar. In this, there are only six days in the first week.

With the help of the WEEKDAY function, we can find the number representing the day of the week of the first day in the provided year.

The Weekday formula would return the number 1 for Sunday, 2 for Monday, and so on by default (please check my note above).

You May Also Like: Free Google Sheets Calendar Template Download and How To

I’ve deduced that number in my formula and added one to get a whole seven days week in the year start.

It may include a few days from last year’s end. The MAX part of the formula solves that (Works when B2 is 1).

The MIN in the second formula works when B2 is 53 or the last week in the year.

Now let us see how to populate a date range from a week number in Google Sheets.

Date Range from Week Number

Our topic is how to find the date or date range from a week number in Google Sheets.

In that, I’ve already explained to you how to find the date from a week number and year. Here is the date range part.

Here is the formula for the cell G2.

Formula 3:

Generic Formula: =ArrayFormula(row(indirect("A"&formula_1&":A"&formula_2)))

With the help of ROW and INDIRECT functions, I’ve populated 7 dates from the start date of a week number.

=ArrayFormula(row(indirect("A"&MAX(DATE(A2,1,1),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7)&":A"&MIN(DATE(A2,12,31),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7+6))))

The number of days may be less if the week number is the start or end of the year.

Finding a date or date range from the week number is too easy in Google Sheets if you know how to use the date functions. That’s all. Enjoy!