Excel ISOWEEKNUM Function – How To Use

The ISOWEEKNUM function is categorized as a DATE/TIME function in Excel and was introduced with Excel 2013. It helps you obtain the ISO week number for a date that you supply, and returns a value between 1 and 54.

Excel ISOWEEKNUM Function

Syntax

The syntax of the ISOWEEKNUM function is as follows:

=ISOWEEKNUM(serial_number)

Arguments:

'serial_number' – This is a required argument where you'll need to supply the serial number, a date value, or a cell reference for the date you want to obtain the ISO week number for.

Important Characteristics of the ISOWEEKNUM function

  • The value supplied in the serial_number argument must be a valid Excel date.
  • The ISOWEEKNUM function refers to the European ISO Standard ISO8601:2000 to return an ISO week number. Under ISO 8601, the weeks start on Monday, and week #1 is the week that contains the year's first Thursday.
  • If you're on MS Excel 2010, you won't have the ISOWEEKNUM function, but you can use the WEEKNUM function and set the return_type argument to 21 to get the same output.
  • If the date you supplied in the serial_number argument is not a number, the function will return a #NUM! error.
  • If the date you supplied in the serial_number argument is not a valid Excel date, the function will return a #VALUE! error.
  • The function is available in MS Excel 2013 and above.

Examples

Let's try to see some examples of the ISOWEEKNUM function.

Example 1 – Plain Vanilla Formula for the ISOWEEKNUM Function

The first thing you should do with any function is to see what it does without all the bells and whistles. The plain vanilla version of the ISOWEEKNUM shows you what the function does when it's not nested or complemented by other functions. Here's the formula you could use to test the ISOWEEKNUM function out in its purest form:

=ISOWEEKNUM(A2)
Plain Vanilla Formula for the ISOWEEKNUM Function

The output here is 3 because the first week includes a Thursday (January 4) and subsequently, there are two Mondays (January 8 and 15), that add to the week number bringing it to 3. This is also the same output you would've received, had you used the WEEKNUM function instead and set the return_type argument to 21, like so:

=WEEKNUM(A2,21)

Plain and easy, right?

However, what if the first week of the year didn't have a Thursday?

Example 2 – ISOWEEKNUM Output with no Thursday in Week 1

Say instead of January 15, 2001 like in the previous example, we supply the date January 15, 2016. The formula remains unchanged, of course:

=ISOWEEKNUM(A2)
ISOWEEKNUM Output with no Thursday in Week 1

However, the output changes to 2 from 3. The reason? Well, January 1, 2016 was a Friday. Therefore, the first week doesn't start until January 4 (Monday), which is the week that contains the year's first Thursday. Subsequently, January 11 (Monday) adds 1 to the week number count and the final return is 2.

Had you used a WEEKNUM function (with the return_type argument omitted), the output would have been 3. Nevertheless, you could still get the same output by setting the return_type argument to 21.

The interesting thing, though, happens when you change the supplied date to January 1, 2016. Technically, the first day of a new year should be week 1, correct? However, the function returns 53 in this case because week 1 doesn't start until the new year's first Thursday.

function returns 53 in this case because week 1 doesn't start until the new year's first Thursday

Also, no, our kindergarten teachers didn't lie to us about there being 52 weeks in a year. However, the function returns 53 because of the system that it follows and the marginal shift in calculating the weeks from the first day of the year.

Example 3 – ISOWEEKNUM Function with the TODAY Function

You can nest functions that can relay a valid date to the ISOWEEKNUM function inside it. For instance, the DATE function and the TODAY function could both be validly nested inside the ISOWEEKNUM function. Let's try to nest the TODAY function inside the ISOWEEKNUM function. Here's what the formula would look like:

=ISOWEEKNUM(TODAY())
ISOWEEKNUM Function with the TODAY Function

The output is 42, which means based on the ISO 8601 standards, it's currently week 42 of 52. You could also calculate how many weeks you've got left until the end of the year by using the following formula:

=52 - ISOWEEKNUM(TODAY())
calculate how many weeks you've got left until the end of the year

Makes complete sense, doesn't it? You're just removing the weeks that have already passed in the current year from the total number of weeks in a year (52) to get the weeks remaining until the end of the year (i.e., 10 in this case).

ISOWEEKNUM vs WEEKNUM Function

The ISOWEEKNUM function has a very specific characteristic. It only counts the number of weeks as per the ISO 8601 standards. The WEEKNUM function, until Excel 2010, could only count week numbers the usual way, i.e., you could choose a starting day for each week but the first week would start as soon as the year does.

However, starting with Excel 2010, the WEEKNUM function also has the capability to calculate the week number based on the ISO 8601 standards by using code 21 in the return_type argument. As of today, the WEEKNUM function can do everything that the ISOWEEKNUM function can, plus a little more.

That's all about the ISOWEEKNUM function. It's a simple function that could make calculating the week number a lot simpler than it would otherwise have been. While you get a good hold of the ISOWEEKNUM function, we'll have another Excel function tutorial ready for you to get your hands dirty with. If you need a refresher, Exceltrick is always a click away.

About Supriya

Supriya, ExcelTrick's Editor-in-Chief, combines her MBA in Human Resources with vast Excel proficiency for data-driven decisions. Her prior role in Corporate HR solidified Excel as her trusted companion. In her leisure, she cherishes family time, gaming, and reading. Get to know Supriya better here.