ISOWEEKNUM Function

What Is The ISOWEEKNUM Function In Excel?

The ISOWEEKNUM function in Excel is a Date and Time Function that returns the ISO week number for a given date in Excel from a provided date value.

The function is in accordance with the ISO 8601 Standard, which states that the week starts on a Monday and that week number 1 is assigned to the first week, which has a Thursday.

This Function is particularly useful in different date and time-related operations and is extremely valuable in scenarios where the ISO 8601 Standard's numbering system is required.

This numbering system promotes consistency and compatibility with International Standards.

What Is The ISO?

The ISO stands for the International Organization for Standardization, is an independent and non-governmental organization that works for international standard development covering technology and manufacturing

ISO week number is the week number of a given data set based on the ISO 8601 standard. It is an international standard used in business and industry to represent 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 communicate more easily with partners and clients in different countries who might be using the same system.

Understanding 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, which represents week 2 of 2023, will begin on Monday, January 8th, and end on Sunday, January 14th.
3. Finally, the last week of the year will be week 52, which begins on Monday, December 25th, and ends on Sunday, December 31st.

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 ISOWEEKNUM 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 February 2nd, 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 it much 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 functions 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, January 6th, 2023, the first Thursday falls on January 5th, 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, January 1st, 2023, is considered week 1, and January 2nd to 8th, 2023, is considered week 2.

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

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