Week Number Functions

If you have a set of dates, you might also want their week numbers. These can be returned using the WEEKNUM or ISOWEEKNUM functions.

What is the difference though?

Function Syntaxes

WEEKNUM( serial_number, [return_type] )

• serial_number — a valid date in serial number format. The DATE function can be used for this purpose.

• [return_type] — the start day of the week. The default is Sunday (System 1).

ISOWEEKNUM( date )

• date — a valid date in serial number format. The DATE function can be used for this purpose.

Start Day Options

Page 1 shows the 10 options in WEEKNUM’s [return_type] argument.

Notice how Monday appears three times and Sunday twice? Excel 2007 and earlier versions only contained 1 – Sunday and 2 – Monday. That changed in Excel 2010 with the addition of eight others.

All but one are based on System 1, with the anomaly being 21 – Monday (System 2), which returns the same result as ISOWEEKNUM.

What is System 1 and System 2?

In the vast majority of the Americas, the week officially begins on Sunday. Across Europe though, it’s Monday, which follows the ISO 8601 international standard.

The rule for week 1 in the American system (System 1) is simple: it starts in the first week containing 1st January.

Meanwhile, ISO 8601 rules (System 2) stipulate the following for week 1:

• it is the first week with at least four days belonging to January
• it contains 4th January and the first Thursday of the year
• its first day is the Monday closest to 1st January

Examples

On page 2, two calendars are present — one based on a start day of Sunday (System 1), and the other on Monday (System 2). Both use formulas that reference the dates in column K, however, the first week number is wrong in the former. That’s because the reference doesn’t cover Q17, which contains 01/01/2022.

At the bottom is a grid showing all week number possibilities for the first 16 days. The column pairs that output identical results are shaded separate colours.

Final Words

Hopefully, that’s cleared up some of the confusion. The key takeaway is to be aware of what number(s) WEEKNUM or ISOWEEKNUM is producing, as it might not suit your circumstances.

I have an article out soon that will demonstrate several Modern Excel calendars and provide a solution to the problem you saw.