Displaying the correct week number:

In this tip, I show you the formula you use to display the week number.
We can determine more than just the correct week number.

In Excel, we have the WEEKNUM function.
However, there is a difference between the WEEKNUM function of Excel, which is based on the U.S. week number, and the ISO 8601: 2000 week number system we use in Europe. Let me explain this.
The American begins counting his weeks from 1 January, i.e, the day on which January 1 falls.
So if January 1 falls on a Wednesday, the American system counts for only half a week and it goes to 2, on the first day of the next week.
I say the first day of the week because for some, Sunday is the first day of the week but, for others it is Monday (more on this in the working examples for us).

The European week number system starts counting a full week from the first day. This is especially evident when it starts on a Monday.
I hope you still follow me, but I’ve given an example here:

You can see in the image above, that in the American system we can have two weeks within seven days.
Whenever January 1 falls on a Monday, they have 1 more week.
They always have some more. 😉
When January 1 falls on a Saturday or 31 december falls on a Monday, they have 2 more weeks.

I will give you two examples with the week number function, which works for us.
1. The WEEKNUM function based on the date in another cell.
This function takes two arguments.
The first one contains the cell where the date is placed, which is cell B1, in this case.
The second is 1 or 2 but, it is optional.
1 if the first day of the week is Sunday.
2 if the first day of the week is Monday, which to my knowledge, is always the case.

When we use 1 as the argument, then it is taken that Sunday is considered as the first day of the week.
For us, Europeans, the week starts on Monday so, we always type 2.

We have a problem, in that, Excel does not give the correct week number.

2. The WEEKNUM function based on the date that you open the Excel file.
We use the NOW function as an argument.
= WEEKNUM (NOW (), 2)

We have a problem, in that, Excel does not give the correct week number.

You should now be thinking, that if you subtract 1, for example, = WEEKNUM (NOW (), 2) -1, the problem is resolved.
That’s true sometimes, but not always. I have explained the reason at the beginning of this tip.

We, Belgians and Dutch can get the correct week number displayed, only with the help of formulas or VBA code.
I’ll give you an example by means of a formula:

3. The correct week number on the date that you open the Excel file:

= INTEGER ((NOW ()-DATE (YEAR (NOW()-WEEKDAY (NOW ()-1) + 4); 1; 3) + WEEKDAY (DATE (YEAR (NOW()-WEEKDAY (NOW ()-1) + 4); 1; 3)) + 5)/7)

4. The correct number week on the basis of a date in another cell:
= INT ((A1-DATE (YEAR (A1-WEEKDAY (A1-1) +4), 1, 3) + WEEKDAY (DATE (YEAR (A1-WEEKDAY (A1-1) +4), 1, 3)) +5) / 7)

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats