SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2143398%22%20slang%3D%22en-US%22%3EFormula%20%3DKalenderwoche(%22AktuellesDatum%22)%20shows%20the%20wrong%20calendarweek.%202021%20has%2052%20and%20not%2053%20weeks.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143398%22%20slang%3D%22en-US%22%3E%3CP%3EI%20got%20several%20documents%20showing%20the%20current%20calendar%20week%20through%20the%20fitting%20formula%2C%20which%20in%20german%20is%20%3DKalenderwoche(%22Date%22).%20I%20was%20told%20today%20by%20colleagues%20that%20the%20current%20calendar%20week%20is%20showing%208%20instead%20of%207.%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20my%20knowledge%20a%20year%20with%2053%20calendar%20weeks%20has%20to%20start%20and%20end%20on%20a%20thursday.%20This%20year%20therefore%20only%20has%2052%20calendar%20weeks%20and%20other%20sources%20show%20the%20same.%20I%20tested%20it%20on%20different%20work%20environments%20and%20all%20show%20the%20same%20error%2053%20instead%20of%2052%20calendar%20weeks%20and%20the%20current%20as%208%20instead%20of%207.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021_calendarweekExcel.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255535i413D22D1ED439A86%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%222021_calendarweekExcel.png%22%20alt%3D%222021_calendarweekExcel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20workaround%20is%20%3DKalenderwoche(%22Date%22-7)%2C%20but%20that%20shouldn't%20be%20a%20solution%20and%20I%20guess%20that%20error%20needs%20to%20be%20resolved.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143439%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20%3DKalenderwoche(%22AktuellesDatum%22)%20shows%20the%20wrong%20calendarweek.%202021%20has%2052%20and%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969771%22%20target%3D%22_blank%22%3E%40PhilipTx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20the%20ISOKALENDERWOCHE%20function%2C%20or%20KALENDERWOCHE%20with%2021%20as%20second%20argument%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISOKALENDERWOCHE(E41)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DKALENDERWOCHE(E41%3B21)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

 

2021_calendarweekExcel.png

 

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

5 Replies
best response confirmed by PhilipTx (Occasional Contributor)
Solution

@PhilipTx 

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

 

=ISOKALENDERWOCHE(E41)

 

or

 

=KALENDERWOCHE(E41;21)

@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. 

@PhilipTx 

You can use

 

=ISOKALENDERWOCHE(HEUTE())

 

or

 

=ISOKALENDERWOCHE(DATWERT("17.02.2021"))

 

or

 

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

@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.