Weeknumber in google sheets seem to skip a week?

user_11059360 0
Viewed 104

I have built a kind of calendar and I am trying to add the week-numbers to it. It's one horizontal calendar in which dates refer to a dynamic calendar next to it. To get the week numbers I am using WEEKNUM(CELL, 2) (the 2 is to take into account that I am starting on a Monday).

But, the weeks are placed as "53, 2, 3, 4...", but in a calendar, such as Ical, it looks like this:

"53, 1, 2, 3, 4...".

So, for some reason my calendar doesn't understand that the week starts on a Monday, even though I use "2". But the weirdest part is that for week 53 it understands it should start on the Monday...

If I place the next references on Sundays it will get week "1" etc, but I am starting all my weeks on Mondays, so now the only way to get it to work is to refer week 53 on Monday (28:dec), and the rest on Sundays the week before...

Why is this happening, and how can I make it work so that Mondays start a new weeknumber, and the sequence is 53, 1, 2, 3...

enter image description here

google-sheets javascript
1 Answers

You can find the meaning of the second argument (i.e. type) to WEEKNUM in the documentation.

Behind the scenes, there are two week numbering "systems" used for this function:

  • System 1 - The first week of the year is considered to be the week containing January 1, which is numbered week 1.
  • System 2 - The first week of the year is considered to be the week containing the first Thursday of the year, which is numbered as week 1. System 2 is the approach specified in ISO 8601, also known as the European system for numbering weeks.

You want system 2. The only value for type that will use system 2 is 21. So use:

=WEEKNUM(CELL, 21)
user_54598390
Related