Feb 17 2021 08:26 AM
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.
Feb 17 2021 08:30 AM
SolutionUse the ISOKALENDERWOCHE function, or KALENDERWOCHE with 21 as second argument:
=ISOKALENDERWOCHE(E41)
or
=KALENDERWOCHE(E41;21)
Feb 17 2021 08:32 AM
@Hans Vogelaar Thank you! That fixes the issue.
Feb 17 2021 08:44 AM
@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.
Feb 17 2021 08:53 AM
You can use
=ISOKALENDERWOCHE(HEUTE())
or
=ISOKALENDERWOCHE(DATWERT("17.02.2021"))
or
=ISOKALENDERWOCHE(DATUM(2021;2;17))
Feb 17 2021 08:56 AM - edited Feb 17 2021 08:58 AM
@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.
Feb 17 2021 08:30 AM
SolutionUse the ISOKALENDERWOCHE function, or KALENDERWOCHE with 21 as second argument:
=ISOKALENDERWOCHE(E41)
or
=KALENDERWOCHE(E41;21)