How to Use ISOWEEKMUM Function in Excel – Examples

Excel includes a plethora of built-in functions that are intended to simplify data analysis and manipulation. The ISOWEEKNUM function in Excel is one example. This function computes the ISO week number for a given date. 

In this article, we’ll go over the ISOWEEKNUM function in Excel in-depth and show you how to use it.

Syntax of ISOWEEKNUM Function

=ISOWEEKNUM(date)

The argument of ISOWEEKNUM Function

date– the date for which you want to calculate the ISO week number. This argument can be typed directly into the function or as a date value cell reference.

Return of the ISOWEEKNUM Function

The ISOWEEKNUM function returns a number between 1 and 53, representing the year’s week number. The ISO week numbering system starts on Mondays.

Example #1: Simple application of ISOWEEKNUM Function in Excel

Suppose you are given some dates and want to return the serial number of the week for each date in a particular year.

Apply the following formula and AutoFill for the remaining cells

=ISOWEEKNUM(B2)

The result is

Example #2: Get the serial number of the week for the current date 

Let’s merge the two functions like this: 

=ISOWEEKNUM(TODAY())

Now, you’re instructing Excel to first calculate the current date using the TODAY() function, and then determine the ISO week number for that date using the ISOWEEKNUM function. The ISO week number for the current date is returned as the output.

In Excel, the TODAY() function returns the current date as a serial number. For example, on March 10, 2023, the TODAY() function would return the serial number 44858, which represents the date March 10, 2023.

The ISOWEEKNUM function would then determine that March 10, 2023, corresponds to ISO week number 10 of the year.

Formula Evaluation

=ISOWEEKNUM(TODAY())

=ISOWEEKNUM(44858)

=10

Similarly, you can count the remaining week of any year using the formula below

=52 - ISOWEEKNUM(TODAY())

As previously discussed, if today’s date is March 10, 2023, the ISO week number for that date is 10. As a result, the formula’s output would be 52 – 10 = 42, which means that there are 42 weeks left in the year.

Example #3: ISOWEEKNUM Function with other Function

Using the following formula, you can count the number of weeks with the same serial number in a given year from a data set.

=SUMPRODUCT(--(ISOWEEKNUM(B3:B7)=F4))

Explanation:

• This formula combines three parts: ISOWEEKNUM, SUMPRODUCT, and the unary double operator (–).
• The ISOWEEKNUM function is being used to calculate the ISO week numbers for a range of dates in cells B3 through B7.
• If ISOWEEKNUM(B3:B7)=F4 the condition is satisfied, and the formula returns a 1; otherwise, the formula returns a 0.
• The SUMPRODUCT multiplies respective elements in the defined arrays (in this case, an array of Boolean values) and needs to return the sum of those products.
• The double unary operator (–), also known as the double negative, is used to convert the comparison operator’s Boolean values to numeric values. This is required because SUMPRODUCT only works with numbers.

Formula Evaluation:

=SUMPRODUCT(--(ISOWEEKNUM(B3:B7)=F4))

=SUMPRODUCT(--({2;5;9;13;13}=F4))

=SUMPRODUCT(--{FALSE;FALSE;FALSE;TRUE;TRUE})

=2

Things to Keep in Mind about ISOWEEKNUM Function

• The ISOWEEKNUM function is only applicable to dates, not times. To calculate the ISO week number for a date and time value, first, extract the date portion of the value using a formula such as =INT(date value).
• The function will return a #VALUE! error if the date is not recognized as a valid Excel serial number or text string.
• Based on the Gregorian calendar, the ISO week counting system has 52 or 53 weeks in a year.

ISOWEEKNUM vs WEEKNUM Function

• The ISOWEEKNUM function uses the ISO 8601 week numbering standard. The week containing January 4th is the first week of the year, according to this standard, and a week always begins on a Monday and ends on a Sunday. The WEEKNUM function, on the other hand, uses your computer’s system settings to determine the first week of the year.
• The ISOWEEKNUM function returns week numbers between 1 and 53. This is because some years have 53 weeks instead of 52 due to an extra day at the start or end of the year. The WEEKNUM function, on the other hand, returns week numbers ranging from 1 to 54, depending on the first day of the year.
• The ISOWEEKNUM function works only in Excel 2013 and later versions, whereas the WEEKNUM function is available in earlier Excel versions.