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:
- If you have the free choice which one to use, go with the “US” system, which means that the first week starts on January 1st. It’s technically much easier, because otherwise you usually run into problem when 1st of January is week 53 of the previous year – although it is obviously another year.
- If you want to use a workaround, don’t use week numbers at all. Try saying “Week of 1/1”, or “Week starting on January 4th” or something similar.
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):
- The first argument is the date you’d like to retrieve the week number from.
- As the second argument, you define which system you would like to use, called the “Return_type”.
| Return type | Week begins on | System |
|---|---|---|
| 1 or omitted | Sunday | 1 (1st of January = Week #1) |
| 2 | Monday | 1 (1st of January = Week #1) |
| 11 | Monday | 1 (1st of January = Week #1) |
| 12 | Tuesday | 1 (1st of January = Week #1) |
| 13 | Wednesday | 1 (1st of January = Week #1) |
| 14 | Thursday | 1 (1st of January = Week #1) |
| 15 | Friday | 1 (1st of January = Week #1) |
| 16 | Saturday | 1 (1st of January = Week #1) |
| 17 | Sunday | 1 (1st of January = Week #1) |
| 21 | Monday | 2 (“European week numbering”) |
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
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
