ISOWEEKNUM to Corresponding Dates in Google Sheets

Published on

We can convert ISOWEEKNUM to corresponding dates in Google Sheets. It is almost similar to converting WEEKNUM to corresponding dates.

With the help of the WEEKDAY function, we can get a date or dates that fall in the week from a week number (WEEKNUM).

I have explained the same here – Find the Date or Date Range from Week Number in Google Sheets.

There is a similar approach to convert an ISOWEEKNUM to a date or date range that falls in the ISO week.

First, let’s try to understand the differences and similarities between the WEEKNUM and the ISOWEEKNUM functions.

You can read about both functions in detail in my guide titled How to Utilize Google Sheets Date Functions.

WEEKNUM and ISOWEEKNUM: Differences and Similarities

The below points are essential to know how to get a date from an ISO week number (ISOWEEKNUM) in Google Sheets.

Both functions take a date and return the corresponding week number.

But in the WEEKNUM function, you can specify ‘type’ (a number representing the day that a week starts on) other than the date.

There are ten types, and they are 1, 2, 11, 12, 13, 14, 15, 16, 17, and 21, which specify the day that a week starts.

For example, type 1 (or leaving it unused) specifies Sunday-Satruday week.

Understanding the below is essential to convert ISOWEEKNUM to corresponding dates in Google Sheets.

In type 21, the WEEKNUM function uses the ISO8601 for numbering the weeks in which the week starts from Monday to Sunday. But the real catch is week # 1.

In ISO8601 (European system of numbering weeks), the first week is the week containing the first Thursday of the year. The same principle applies to ISOWEEKNUM.

As far as I know, using type 21 in WEEKNUM is equal to using the ISOWEEKNUM function.

ISOWEEKNUM to Corresponding Dates in Google Sheets

Sample Data:

Find Pay Start and End Date from ISO Week Number

Let’s see how to find the pay start date and pay end date from ISOWEEKNUM in Google Sheets.

F2 Formula (ISOWEEKNUM Start Date):

=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7)

G2 Formula (ISOWEEKNUM End Date):

=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7+6)

Drag these formulas down, select the result, and apply Format (menu) > Number > Date.

ISOWEEKNUM to Corresponding Dates - Example

I have an array formula to convert ISOWEEKNUM in cells (cell range) to corresponding dates, but we will start with the non-array solution.

Anatomy of the Formulas

Here are some key points.

In the ISO week numbering system (European system of numbering weeks), the first Monday in Week # 1 is always between December 29 and January 4.

The following table may give you an idea of how it works.

If Thursday =First Monday (ISO Week Starts)
129 December
230 December
331 December
41 January
52 January
63 January
74 January

So, the first Monday immediately before January 5 (please see column # 2 in the table above) will be the ISO week # 1 start date.

How do we find that in Google Sheets?

Let’s forget the above-said numbering system and try to find the Monday of any given date falls.

Monday of any given date falls: =date-weekday(date)+2

The date returned by the above formula may be immediately before or after the given date.

The formula to find the Monday that is immediately before the given date is =date-weekday(date-2)

Based on the above logic;

The Monday that is immediately before 5/1/year is =date(year,1,5)-weekday(date(year,1,3))

Note:- In this formula ‘date’ is a function name.

The above formula is enough to convert ISOWEEKNUM to the corresponding week start date in Google Sheets. But we want to find the week end date too.

So what we will do here is to find the last Monday of the previous year =date(year,1,-2)-weekday(date(year,1,3))and add ISOWEEKNUM*7 days for the week start and ISOWEEKNUM*7+6 for the week end date.

=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7)
=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7+6)

The Role of IF Logical Test in ISOWEEKNUM to Corresponding Date Formula

Let’s discuss the IF logical part which is if(isoweeknum(test)=D2,test,"").

The name “test” represents the above green and orange highlighted part of the formula.

In other words, it represents the formula that converts ISOWEEKNUM to corresponding week start and end dates.

The formula may return an unwanted result when we input 53 in D2 and the corresponding year in C2 only has 52 weeks.

The IF logical part solves that.

The LAMBDA shortens the logical test by assigning the name “test” to the ISOWEEKNUM to date formula.

If we want, we can avoid dragging the F2 and G2 formulas down by converting them to array formulas using the MAP function.

Here you go!

F2:

=iferror(map(C2:C,D2:D,lambda(year,week,lambda(test,if(isoweeknum(test)=week,test,""))(date(year,1,-2)-weekday(date(year,1,3))+week*7))))

G2:

=iferror(map(C2:C,D2:D,lambda(year,week,lambda(test,if(isoweeknum(test)=week,test,""))(date(year,1,-2)-weekday(date(year,1,3))+week*7+6))))

Sample_Sheet_251220

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. This sadly fails for e.g.:

    26/12/2022
    27/12/2022
    28/12/2022
    29/12/2022
    30/12/2022

    by incorrectly setting the isoweek’s start date to 01/01/2022

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.