Formula =Kalenderwoche("AktuellesDatum") shows the wrong calendarweek. 2021 has 52 and not 53 weeks.

[PhilipTx]

I got several documents showing the current calendar week through the fitting formula, which in german is =Kalenderwoche("Date"). I was told today by colleagues that the current calendar week is showing 8 instead of 7. 

From my knowledge a year with 53 calendar weeks has to start and end on a thursday. This year therefore only has 52 calendar weeks and other sources show the same. I tested it on different work environments and all show the same error 53 instead of 52 calendar weeks and the current as 8 instead of 7. 

 

The workaround is =Kalenderwoche("Date"-7), but that shouldn't be a solution and I guess that error needs to be resolved. Thank you. 

[#2 · Accepted solution]

@PhilipTx 

Use the ISOKALENDERWOCHE function, or KALENDERWOCHE with 21 as second argument:

=ISOKALENDERWOCHE(E41)

or

=KALENDERWOCHE(E41;21)

[#3]

@Hans Vogelaar Thank you! That fixes the issue. 

[#4]

@Hans Vogelaar There is one issue that bothers me, because I'm basically already setting the year with giving the full date, but excel doesn't recognize that in =Kalenderwoche(17.02.2021), right? So if I'm not mistaken that should be fixed. 

[#5]

@PhilipTx 

You can use

=ISOKALENDERWOCHE(HEUTE())

or

=ISOKALENDERWOCHE(DATWERT("17.02.2021"))

or

=ISOKALENDERWOCHE(DATUM(2021;2;17))

[#6]

@Hans Vogelaar Yes, I could do that, but that still doesn't explain why the right calendar week isn't shown even when passing on the right information (day. month.year). I can't use (heute()) due to the documents being bound to each week.

I fixed it with =ISOKALENDERWOCHE("DATE"). If I use =KALENDERWOCHE("DA,,MONTH,YEAR) it should recognize the correct answer.