Site icon Professor Excel

How to Insert Week Number & Deal With Different Week Systems

Especially when working with calendars and dates, you might want to use the numbers of weeks. Unfortunately – and especially – when you work internationally, it is not as simple as it sounds. You first have to define the week number system. In this article, we will take a look at the two basic week number systems and then the WEEKNUM and ISOWEEKNUM functions.

The problem when working internationally: Which week number is it right now?

Working with times and dates sounds easy, right? Believe me, it is not. If you have some time, there is a great video about on YouTube about it:

But what does that have to do with you? Well, the question is, which week do we have right now?

Choose the week system first

In 2021, the problem is that the year has started on a Friday. So, is this Friday, 1st of January 2021, week number 1? Or 53 of the previous year? Both systems are common, in the US rather the 1st of January = week #1 whereas in Europe rather “First Thursday in a year = week #1”. If you work internationally, you have to decide, which one to use.

My personal recommendations here:

Which weekday is the first day of the week? Sunday or Monday?

Again, there are regional differences of which day a week starts with. In the US it seems common that a week starts on Sunday whereas in the EU on Monday. This question is usually faster to answer: I have seen that in large cooperations it either depends on your individual or the headquarter location.

Summary of the week system

That was quite theoretical, right? In summary, please see the table below. It shows the comparison of systems, starting week day and Excel functions.

How to insert the week number with the WEEKNUM function

The WEEKNUM function has 2 arguments (the numbers are corresponding to the screenshot):

WEEKNUM function in Excel
Return typeWeek begins onSystem
1 or omittedSunday1 (1st of January = Week #1)
2Monday1 (1st of January = Week #1)
11Monday1 (1st of January = Week #1)
12Tuesday1 (1st of January = Week #1)
13Wednesday1 (1st of January = Week #1)
14Thursday1 (1st of January = Week #1)
15Friday1 (1st of January = Week #1)
16Saturday1 (1st of January = Week #1)
17Sunday1 (1st of January = Week #1)
21Monday2 (“European week numbering”)
The second argument is the return type and describes the system used.

The picture above shows a simple example: We want to display the number of the week in a schedule. Therefore, cell E5 has this formula:

=WEEKNUM(B3,1)

The cell B3 has as the first part of the WEEKNUM function the date we want to get the number from. The second part determines the system, in this case the week starts on Sunday and the first week of the year starts on 1st of January.

ISOWEEKNUM: Excel function for standardized week numbers

ISOWEEKNUM function only has one argument and relies on the European week number system.

The ISOWEEKNUM function was first introduced with Excel 2013. It only has one argument: The date you want to retrieve the week number from.

This function uses the “European week numbering system”. That means, the first week of a year is the week that has the first Thursday. Also, weeks start on Monday (and not Sunday).

Effectively, the ISOWEEKNUM function is the same as WEEKNUM with return type 21. So, =ISOWEEKNUM(B3) is equal to =WEEKNUM(B3,21).

Image by Amber Avalona from Pixabay

Exit mobile version