(Überarbeitet am 06.06.2021)
Excel bietet mit der Funktion KALENDERWOCHE die Möglichkeit, sich zu einem gegebenen Datum die dazugehörige Kalenderwoche ausgeben zu lassen.
Was sich auf den ersten Blick ganz praktisch anhört, könnte in der Praxis allerdings zu Verwirrung führen: Wer hat sich denn schon mal Gedanken gemacht, wann in einem Jahr eigentlich genau die KW 1 beginnt?
Ich jedenfalls lange Zeit nicht. Zumindest nicht, bis ich mich näher mit der KALENDERWOCHE-Funktion beschäftigt habe. Und dabei musste ich feststellen, dass KW 1 nicht immer KW 1 ist und dass der schlaue Excel-Anwender vielleicht plötzlich 2 Wochen mehr Urlaub im Jahr haben könnte…
Die Funktion KALENDERWOCHE
Die im folgenden verwendete Beispieldatei kannst du dir bei Bedarf hier herunterladen.
Für diejenigen, die diese Funktion noch nicht kennen: Damit lässt sich die Kalenderwoche zu einem gegebenen Datum ermitteln.
=KALENDERWOCHE(Datum;Typ)
Für den ersten Parameter „Datum“ wird eine Zahl oder ein Verweis auf eine Zelle mit einem Datum erwartet.
Der zweite Parameter „Typ“ ist eine Zahl, die den Rückgabewert bestimmt. Die möglichen Typ-Werte hängen von der jeweiligen Excel-Version ab.
Excel 2007:
Falls du tatsächlich noch diese Excel-Version im Einsatz haben solltest, sind die Möglichkeiten sehr übersichtlich. Hier gibt es nur diese beiden Typen zur Auswahl:
Typ 1: Woche beginnt am Sonntag
Typ 2: Woche beginnt am Montag
Wenn also, wie im europäischen Raum üblich, die Woche am Montag beginnt, sollte man den Typ 2 angeben (Hinweis: Wird der Typ ganz weggelassen, verwendet Excel automatisch Typ 1). Und hier wird es schon zum ersten Mal interessant. Das folgende Bild zeigt die Gegenüberstellung der berechneten Kalenderwoche für das angegebene Datum, abhängig vom Typ:
KALENDERWOCHE in Excel 2007
Die wesentliche Information ist, dass mit beiden Typen die KW 1 immer mit dem 01.01. des Jahres beginnt. Der Unterschied ist lediglich, dass bei Typ 1 eine neue Woche mit dem Sonntag beginnt, bei Typ 2 jedoch mit dem Montag.
Auch im Jahreswechsel 2013 auf 2014 zeigt sich ein ähnliches Bild:
KALENDERWOCHE in Excel 2007
Wie man sieht, endete 2013 die KW 53 entweder nach 3 Tagen (bei Typ 1) oder bereits nach 2 Tagen (bei Typ 2).
Excel 2010 und neuer:
Hier gibt es deutlich mehr Möglichkeiten, über den Typ den jeweiligen Wochenanfang zu beeinflussen:
Typen (seit Excel 2013)
Die aber wichtigste Verbesserung ist Typ 21: Dieser verwendet nämlich die in Europa geltende ISO-Norm 8601 zur Ermittlung der ersten Kalenderwoche. Und die besagt, dass KW 1 immer die Woche mit dem ersten Donnerstag im Januar ist.
Wie im folgenden Bild deutlich zu sehen ist, gibt es abhängig vom verwendeten Typ deutliche Unterschiede, wann die letzte Kalenderwoche eines Jahres rechnerisch endet und wann die erste des neuen Jahres beginnt:
Der wichtige Typ 21 (Jahr 2019/2020)
… und für den Jahreswechsel 2021/2022:
Typ 21 für 2021/2022
Eine erstaunliche Erkenntnis bringt aber die Übersicht für das schon etwas länger zurückliegende Jahr 2012:
54 versus 52 Kalenderwochen
Während bei den Typen 2 und 11 das Jahr 2012 ganze 54 Kalenderwochen lang war, endete es mit Typ 21 schon nach 52 Wochen, also 2 Wochen früher. Oder anders ausgedrückt:
54 - 52 = 2 Wochen weniger Arbeit = 2 Wochen mehr Urlaub ?
Wer schon mal seinen Urlaub planen möchte: Eine ähnliche Konstellation ergibt sich im Jahr 2024.
(Da wir gerade beim Thema Urlaub sind: Vielleicht wäre ja der Excel-Urlaubsplaner etwas für Dich!)
Alle, die sich also mit dieser Excel-Funktion mal eben schnell eine Kalenderwoche ausrechnen lassen möchten, sollten diese kleinen, aber feinen Unterschiede im Hinterkopf behalten. Vor allem, wenn der Chef nicht ganz so gut in Excel ist 🙂
Die bessere Alternative: ISOKALENDERWOCHE
Nach den nicht ganz ernst gemeinten Ausführungen über den möglichen Zusatzurlaub auf Basis der unterschiedlichen Typen in der KALNDERWOCHE-Funktion möchte ich dir (nun aber ernsthaft!) die bessere Variante empfehlen:
Die Funktion ISOKALENDERWOCHE, die mit der Excel-Version 2013 eingeführt wurde:
=ISOKALENDERWOCHE(Datum)
Die kommt nämlich ganz ohne verwirrende Typen aus und liefert automatisch die in Europa vorherrschende Berechnungsweise auf Basis der eingangs schon erwähnten ISO-Norm. Und damit das identische Ergebnis wie KALENDERWOCHE mit Typ 21:
Funktion ISOKALENDERWOCHE (ab Excel 2013)
Wenn du also eine einigermaßen aktuelle Excel-Version einsetzt, dann ist diese Funktion dein neuer Freund. Vielleicht erkennt dein Chef jetzt endlich dein Potenzial und gewährt dir auch so einen kleinen Zusatzurlaub 😉
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
mit der Funktiom Kalenderwoche stehe ich gerade auf Kriegsfuss.
Ich habe einen Monatskalender erstellt, welcher auch soweit funktioniert. Bei Einfügen der Funktion „Kalenderwochen“ (=KALENDERWOCHE(D3;21) gibt Excel mir für den 26.12.2016 die Kalenderwoche 21 zurück.
Vieleicht habe ich ja nur einen Denkfehler und du kannst mir weiterhelfen.
Viele Grüße
Markus
Hallo Markus,
das hört sich sehr merkwürdig an. Und in Zelle D3 steht wirklich ein Datum? Was kommt denn heraus, wenn du es nur mit =KALENDERWOCHE(D3) versuchst, also ohne den zweiten Parameter? Welche Excel-Version hast du im Einsatz?
Schöne Grüße,
Martin
Hallo Martin,
erstmal Daumen hoch für die tolle Seite und die vielen Tipps.
Leider habe ich eine Problemstellung mit den Kalenderwochen, die ich bisher nicht lösen konnte.
Die Aufgabe ist es verschiedenen Datumsangaben über mehrere Jahre als Kalenderwochen dazustellen, welche anschließend noch mit Verweisen verknüpft werden müssen. Daher benötige ich eine zusätzliche Zelle, die mir dies (z.B. mit der Verketten-Funktion) wiedergibt =VERKETTEN(KALENDERWOCHE(A1;21);“-„;JAHR(A1).
Da die Korrekte Kalenderwoche wieder gegeben werden soll, möchte ich den Typ 21 verwenden. Jetzt ist das Problem, dass z.B. der 31.12.2012 oder auch der 31.12.2018 zwar korrekt mit der Kalenderwoche 1 angezeigt wird, jedoch die Jahreszahl der Kalenderwoche nicht automatisch mitläuft.
Vielleicht hast Du ja eine Idee.
Vielen Dank und Gruß
Thomas
Hallo Thomas,
danke für das nette Feedback.
Für dein Problem könntest du über eine WENN-Abfrage prüfen, ob die KW = 1 und der Monat = 12 ergibt und davon abhängig zum Jahr einfach 1 addieren. Also etwa so:
=VERKETTEN(KALENDERWOCHE(A1;21);“-„;WENN(UND(KALENDERWOCHE(A1;21)=1;MONAT(A1)=12);JAHR(A1)+1;JAHR(A1)))
Schöne Grüße,
Martin
Danke für den Tip, so funktioniert es wunderbar.
Als Ergänzung, falls noch jemand nach dem Problem sucht, habe ich die Formel noch mit dem umgekehrten Fall erweitert, daher wenn die ersten Tage im Jahr noch in die Kalenderwoche 52 oder 53 des Vorjahres fallen.
Sieht dann so bei mir aus:
=VERKETTEN(KALENDERWOCHE(A1;21);“-„;WENN(KALENDERWOCHE(A1;21)>=52;WENN(MONAT(A1)=1;JAHR(A1)-1;JAHR(A1));WENN(KALENDERWOCHE(A1;21)=1;WENN(MONAT(A1)=12;JAHR(A1)+1;JAHR(A1));JAHR(A1))))
Hallo Thomas,
danke, dass Du hier Deine Lösung zeigst, das hilft bestimmt dem einem oder anderen Leser.
Schöne Grüße,
Martin
Hallo Martin, ich hab es leider noch nicht ganz verstanden, wie mir Excel zu 2 Wochen mehr Urlaub verhelfen soll. Ich hab die einzelnen kw nach nettoarbeitstagen nachgerechnet und ich bekomm das selbe ergebnis. steh auf der Leitung. vielleicht versuchst du es mir nochmal zu erklären, danke im Voraus und mit freundlichen Grüßen, Daniel
Hallo Daniel,
das mit den 2 Wochen war natürlich nur etwas provokant gemeint. Es ging mir darum zu zeigen, dass man je nachdem, welchen Typ man in der KALENDERWOCHE-Funktion verwendet, im Extremfall bis zu zwei Wochen Differenz als Ergebnis für ein Datum erhält. So kann der 30.12. beispielsweise noch in KW 52 liegen, mit einem anderen Typ aber schon in KW 1 des Folgejahres.
Das hat mit echten Unterschieden in den Arbeitstagen natürlich überhaupt nichts zu tun, es geht lediglich um die Kalenderwochen. Tut mir also leid, wenn ich dich mit dem Urlaub jetzt enttäuschen muss 😉
Schöne Grüße,
Martin
Danke für den Beitrag.
Deine Abbildungen zeigen ganz klar die Unterschiede.
Das dürfte jeder verstehen.
Mein Fazit: ISOKALENDERWOCHE genügt für den Gebrauch in Deutschland.
Hallo Gerhard,
vielen Dank, das sehe ich genau so.
Schöne Grüße,
Martin