ISOWEEKNUM Function

The ISOWEEKNUM function in Excel returns the ISO week number for a given date in Excel.

ISO what? Now what's an ISO week number?

ISO week number is the week number of a given data based on the ISO 8601 standard. It is an international standard used in business and industry for representing dates, times, and time intervals.

The ISO 8601 Standard was first published by the International Organization for Standardization (ISO) in 1988 and was most recently amended in 2022.

But what's the significance of the ISO week number? Why do businesses bother using it?

The reasons are quite simple - it helps them with a standardized and consistent way of counting weeks, which helps with reporting, planning, and compliance.

Since it is an internationally accepted standard, it helps businesses to communicate more easily with partners and clients in different countries since they might be using the same system.

What is the ISOWEEKNUM function?

ISOWEEKNUM is categorized as a Date & Time function that returns the ISO week number for a referenced date.

Under this system, the first week of the year is the one that contains the first Thursday, which is used as a reference to calculate the first week of the year.

Let's take an example to understand how the ISO week numbering works:

1. Suppose the year is 2023. January 5th falls on a Thursday. Thus, under the ISO week numbering standard, the first week of the year contains the 5th of January, which runs from Sunday, the 1st of January, to Saturday, the 7th of January. That's week 1 of 2023 
2. The next week will begin on the 8th of January, Monday, and end on Sunday, the 14th of January, representing week 2 of 2023.
3. Finally, the last week of the year will be week 52, which begins on Monday, the 25th of December, and ends on Sunday, the 31st of December.

However, there's a catch.

If a date falls in the last week of a year, wherein most of the week falls in the consecutive year, it is considered part of the following year.

For example, if the 31st of December was on Monday and part of week 52, wherein most of the days fell in 2024, then the 31st of December is considered part of the first week of 2024.

Although there's a lot of explanation for the function, the syntax for ISOWEEKNUM, on the other hand, is quite simple, as illustrated below:

=ISOWEEKNUM(date)

where

• Date: (required) the date for which the Iso week number will be returned

Example of the function

Suppose the current date is the 7th of May, 2023. First, you need to determine the iso week for the given date. The data looks as illustrated below:

We will use the formula =ISOWEEKNUM(C2) in cell C4, which gives the result of 18.

Thus, we can say that the 7th of May 2023 falls in the 18th week of the year.

Let's see another example of getting the Iso week number in Excel. Suppose we have the date as the 2nd of February 2024.

As per the Iso week standards, the first week is the one that contains the first Thursday. Thus, if we see the calendar, we will find that the 4th of January 2024 falls on Thursday. Thus, the first week would be from Monday, i.e., the 1st of January 2024, till Sunday, i.e., the 7th of January 2024.

The second week will begin on the 8th of January 2024, and so on.

Returning to our date of the 2nd of February 2024, the data looks as illustrated below:

After using the formula =ISOWEEKNUM(C2) in cell C4, we get the result of 5.

Thus, the 2nd of February 2024 will fall in the fifth week as per the ISO week standards.

WEEKNUM vs. ISOWEEKNUM Function

We know you were aware of the WEEKNUM function. However, many Excel wizards use WEEKNUM a lot more than its counterpart.

However, ISO week standards are slowly becoming mandatory in European countries and are soon expected to be accepted worldwide.

This makes it even more important to understand the difference between both functions.

The WEEKNUM is categorized as a date & time function that returns the week number from 1 to 53 for a given date.

Here, the rules are completely different. We don't need to look at the 4th of January to determine the first week. Instead, the function starts counting the week from the date of the 1st of January itself.

However, the function does allow you to select the weekday from which the week begins. So, for example, it will allow you to reference a date and then give you the option of choosing the beginning weekday.

The syntax for the WEEKNUM function is:

=WEEKNUM(serial_number, [return_type])

where,

• serial_number: (required) the serial number of the referenced date, which will be used to return the week number. Dates are typically stored as serial numbers in Excel from 1, i.e., the 1st of January 1900 till the 31st of December 9999.
• return_type: (optional) determines what day the week begins. The argument can accept different values, as illustrated below:

However, when the value is equal to 21, week one is assigned to the week that contains the first Thursday of the year. That's what the ISOWEEKNUM function does; it assigns the first-week number based on the ISO 8601 standards.

Let's see an example to understand how the WEEKNUM function works.

Suppose we have the data as illustrated below:

This example will give you a broader view of the results based on the return_type argument.

We will use the formula =WEEKNUM(B3,1) in cell D3 giving us the week number for the date the 6th of January 2023 as 1.

As per the argument value in column C, we will accordingly amend the formula, which gives the rest of the results as follows:

As you can see, in some cases, we get the weeknum as 1, while the weeknum is returned as 2 in other cases.

This is due to the difference in the week's starting point for the corresponding date. For example, when the return_type argument is equal to 11.

On the other hand, if the value is equal to 16, the week begins on Saturday.

This small difference eventually brings a major difference in the week number for the same date.

Let's see another example to compare how the ISOWEEKNUM and the WEEKNUM function differ. Suppose we have the following dates in Excel as illustrated below:

Let's consider Monday as the day when the week starts for the WEEKNUM function. Thus, the return_type argument becomes 11 for all the dates.

We will use the formula =WEEKNUM(B3,11) in cell C3 and drag it down till cell C7, which gives the result as

Similarly, we will use the formula =ISOWEEKNUM(B3) in cell D3 and drag it down till cell D7 which gives the result:

Thus, comparing both results shows that the week number starts early using the formula WEEKNUM. 

For the date, the 6th of January 2023, the first Thursday falls on the 5th of January 2023, and thus it eventually becomes the first week.

As for the WEEKNUM function, which we have assumed to start from Monday, the date that falls on Monday is the 2nd of January 2023. Before that, we have the 1st of January 2023, a Sunday. 

Thus, the 1st of January 2023 is considered week 1, and the 2nd to the 8th of January is considered week 2.

The rest of the numbers follow a similar trend wherein the week and iso week numbers are eventually derived from the date.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful  WSO resources:

• COVARIANCE.P Function
• DEVSQ Function
• FORECAST.LINEAR Function
• LOGNORM.DIST Function
• PRICEDISC Function