Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Datum/Zeit

Beitrag: Ermittlung der Kalenderwoche nach DIN-Norm *

Aufgabe
Die Funktion KALENDERWOCHE() rechnet bis einschließlich Excel 2007 falsch bzw. nach amerikanischen Standard. (Zudem muß bis einschließlich Excel 2003 das Add-In Analysefunktionen geladen sein.)

Beispiel:
       A               B               C       
1 Datum  falsch  richtig  
2 31.12.1999  53  52  
3 01.01.2000  1  52  
4 02.01.2000  1  52  
5 16.09.2000  38  37 


1976 wurde der Wochenbeginn auf Montag festgelegt. (Vorher gab's die Kalenderwoche in dem Sinne also nicht.)
Die erste Woche des Jahres ist definiert als die Woche, in die mindestens 4 Tage fallen = DIN 1355. Entspricht der internationalen Norm ISO 8601 (1988); übernommen von der EU als EN 28601 (1992) und in Deutschland als DIN EN 28601 (1993) umgesetzt.
Vereinfacht: die Woche, die den 04. Januar enthält.

Amerikanisch: die Woche, die den 01. Januar enthält.

Lösung
das Datum (größer 31.12.1900) steht in A1

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)
Alternative von Franz Pölt:
=KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)
Zelle benutzerdefiniert formatieren mit 0". KW"

ab Excel 2010:
Wenn man den 2. Parameter Zahl_Typ der Funktion KALENDERWOCHE mit 21 belegt, erhält man nun auch für Deutschland die richtigen Ergebnisse:
=KALENDERWOCHE(A1;21)
funktioniert tadellos - ab Excel 2013 auch =ISOKALENDERWOCHE(A1)

Soll zusätzlich zur KW noch die entsprechende Jahreszahl ausgegeben werden - ist nur kompliziert um Silvester herum, denn z.B. der 03.01.2010 ist 53/2009, dann (eine der obigen Formeln steht in B1):
=B1&"/"&MIN(JAHR(A1-1-REST(A1-2;7)+4);JAHR(A1-REST(A1-1;7)+4))
verkürzt von Erich Gier:
=B1&"/"&JAHR(A1+3-REST(A1-2;7))

Die letzte KW ungleich 1 (52 oder 53) des Jahres aus A1 ist (Franz Pölt):
=52+(TAG(346-REST("2.1."&JAHR(A1);7)+("1-"&JAHR(A1)))<8)

Variante ab Excel 2010 (Josef B):
=MAX(KALENDERWOCHE((12&-JAHR(A1))+{30;23};21))

bzw. allgemein, wenn in der Systemsteuerung ein anderes Datumsformat als TT.MM.JJJJ eingestellt wurde (Horst Schmid):
=52+(TAG(346-REST(DATUM(JAHR(A1);1;2);7)+(DATUM(JAHR(A1);1;1)))<8)

Die Funktion KALENDERWOCHE ist nicht matrixfähig:
=KALENDERWOCHE(A1:A10;21) = #WERT!
Mit einem kleinen Trick ist sie es dann aber doch:
=KALENDERWOCHE(+A1:A10;21)
Ist bei ISOKALENDERWOCHE unnötig.