So berechnen Sie Wochen bzw. Kalenderwochen mit Excel richtig
In Microsoft Excel steht Ihnen mit der Funktion KALENDERWOCHE eine Funktion zur Verfügung, mit der Sie passend zu einem spezifischen Datum die Kalenderwoche anzeigen können. Beispielsweise können Sie sich automatisch anzeigen lassen, in welcher KW ein Termin stattfindet. Die Tabellenfunktion KALENDERWOCHE in Excel hat gleichzeitig ihre Tücken. Das liegt daran, dass eine Kalenderwoche in Deutschland, Österreich und der Schweiz anders definiert ist als in europäischen Nachbarländern oder den USA. Wie Sie trotzdem die korrekte Kalenderwoche errechnen und welche Unterschiede zwischen der Kalenderwoche in der DACH-Region und anderen Ländern bestehen, zeigt dieser Artikel.
Die Funktion KALENDERWOCHE in Excel
Die Funktion KALENDERWOCHE berechnet die Kalenderwochen in den europäischen Versionen von Excel ebenfalls anhand des amerikanischen Standards. Der erste Tag im Jahr wird grundsätzlich der Kalenderwoche 1 zugeordnet.
Der Syntax für die Berechnung der Kalenderwoche lautet:
=KALENDERWOCHE(DATUM)
In Deutschland, Österreich und der Schweiz wird eine Kalenderwoche abweichend definiert. Es ist grundsätzlich die Woche, die als erste Woche im Jahr aus mindestens vier Tagen besteht. Fällt der erste Januar auf einen Freitag, Samstag oder Sonntag, liefert die Tabellenfunktion KALENDERWOCHE ein für Europa falsches Ergebnis.
Die Kalenderwochen in Excel korrekt ermitteln – so funktioniert es
Um für Deutschland, Österreich oder die Schweiz die korrekte Kalenderwoche darzustellen, benötigen Sie eine Formel. Diese muss beispielsweise für die Zelle C4 wie folgt lauten:
=KÜRZEN((A4-DATUM(JAHR(A4-REST(A4-2;7)+3);1;REST(A4-2;7)-9))/7)
Die Formel ist komplex und umfangreich. Um die Funktionsweise verständlich zu erläutern, betrachten wir die einzelnen Bestandteile der Formel getrennt:
- Innerhalb der Formel taucht mehrmalig die Funktion REST(A4-2;7) auf. Diese Funktion teilt das Datum durch 7 und liefert als Ergebnis den resultierenden Rest. Diese Funktion liefert für Montage den Wert 0, für Dienstage den Wert 1 bis zu einem Wert von 6 für Sonntage.
- In der Formel wird vom Datumswert die Zahl 2 abgezogen. Der Grund dafür besteht darin, dass es sich beim Basisdatum 1.1.1900 um einen Sonntag gehandelt hat. Geteilt durch 7 ergibt sich ein Rest von 1. Aus diesem Grund würde der Montag zur Zahl 2 aufgelöst. Um die Tageszählung auf 0 für Sonntag, 1 für Montag usw. zu korrigieren, wird vom Datum die Zahl 2 abgezogen.
- Ausgehend von dieser Wochentags-Berechnung liefert der Teil (JAHR(A4-REST(A4-2;7)+3) für die ersten drei Tage des Jahres einen um 1 verringerten Jahreswert, sofern es sich bei diesen Tagen um Freitage, Samstage oder Sonntage handelt.
- Innerhalb der Funktion DATUM fließt dieser Jahreswert in ein Datum ein. Dieses Datum wird generell mit dem zweiten Argument 1 für den Monat Januar gebildet.
- Als Tageswert übergeben Sie der DATUM-Funktion die oben beschriebene REST-Funktion abzüglich des Wertes 9. Daraus ergibt sich grundsätzlich ein negativer Tageswert. Aus der Formel resultiert, dass das von DATUM berechnete Datum generell im Dezember liegt.
- Für die ersten drei Tage des Jahres liegt es allerdings im Dezember des vorvorletzten Jahres, sofern es sich bei diesen drei Tagen um Freitage, Samstage oder Sonntage handelt.
- Dieses von der DATUM-Funktion berechnete Datum ziehen Sie vom Datum in Spalte A ab, teilen die Differenz durch 7 (für die 7 Tage einer Woche) Diese ganze Berechnung setzen Sie noch innerhalb einer KÜRZEN-Funktion ein, um ausschließlich ganzzahlige Werte zu erhalten.
Alternativ: Das Funktionsargument WOCHENSTART nutzen
Eine weitere Möglichkeit, um in Microsoft Excel mit der KALENDERWOCHE-Funktion zu arbeiten und korrekte Ergebnisse zu erzielen ist die Ergänzung der Formel um das Funktionsargument Wochenstart. Ähnlich wie die oben gezeigte Formel bedeutet es Aufwand, die Formel aufzubauen.
Das Funktionsargument WOCHENSTART kann die beiden unterschiedlichen Systeme zur Berechnung der Kalenderwoche unterscheiden:
- System 1 (Amerikanisches System) – gibt KW 1 als die Woche aus, die den ersten Januar umfasst.
- System 2 (Europäischer Standard nach ISO 8601) – als KW 1 wird die Woche dargestellt, die den ersten Donnerstag des Jahres umfasst.
Nutzen Sie das Funktionsargument WOCHENSTART und die Kalenderwoche-Funktion müssen Sie grundsätzlich als Argument die folgenden Zahlen eintragen:
Montag | 2 oder 11 |
Dienstag | 12 |
Mittwoch | 13 |
Donnerstag | 14 |
Freitag | 15 |
Samstag | 16 |
Sonntag | 1 oder 17 |
Die KALENDERWOCHE-Funktion mit dem Funktionsargument WOCHENSTART verknüpfen in 6 Schritten
Um die Kalenderwoche-Funktion mit dem Funktionsargument Wochenstart zu verknüpfen, gehen Sie wie folgt vor:
Geben Sie ein Datum in Ihre Excel-Tabelle ein.
Klicken Sie mit der linken Maustaste in die Zelle, in die Sie die Kalenderwoche einarbeiten möchten.
Wechsel Sie in die Registerkarte FORMELN und klicken Sie mit der linken Maustaste auf „Funktion einfügen.“
Funktion einfügen in Excel Geben Sie KALENDERWOCHE in das Suchfeld ein.
Die Funktion KALENDERWOCHE suchen Nachdem Sie die Funktion KALENDERWOCHE gefunden haben, bestätigen Sie die Auswahl mit OK.
Sobald Sie sich im Eingabefeld der Funktion befinden, geben Sie die Formelbestandteile wie folgt ein:
- Fortlaufende_Zahl = Zelle, die das Datum enthält, zum Beispiel A1.
- Zahl-Typ = Zahlenwert für den Wochentag gemäß oberer Tabelle.
Formelbestandteile für die Kalenderwoche eingeben
Die Kalenderwoche wird korrekt nach ISO 8601 ausgegeben.
Wie Sie den Anfang und das Ende einer beliebigen Kalenderwoche in Excel berechnen
Bei der professionellen Terminplanung spielen Kalenderwochen eine entscheidende Rolle. Häufig werden Termine für eine bestimmte Kalenderwoche geplant oder vereinbart. In diesem Fall müssen Sie
- Wissen, in welcher Kalenderwoche ein bestimmtes Datum liegt sowie
- Erfahren, an welchem Datum eine bestimmte Kalenderwoche beginnt oder endet.
Diese Information hilft Ihnen zum Beispiel bei der Planung von Fertigungsterminen im industriellen Umfeld oder der Vereinbarung von Liefer- oder Abrechnungsterminen.
Über eine spezielle Formel ermitteln Sie bequem den Beginn und das Ende einer beliebigen Kalenderwoche. Die Formel besitzt den folgenden Aufbau:
=TEXT(DATUM(JahrZahl;1;7*KalWoch-3-
WOCHENTAG(DATUM(JahrZahl;;);3));“TT.MM.JJ“)
&“ - “&TEXT(DATUM(JahrZahl;1;7*KalWoch+3-
WOCHENTAG(DATUM(JahrZahl;;);3));“TT.MM.JJ“)
Sie übergeben der Formel zwei Argumente: Mit dem Argument JahrZahl legen Sie das Jahr fest, in dem die Kalenderwoche liegt. Die Nummer der Kalenderwoche übergeben Sie mit dem Argument KalWoch.
Als Ergebnis liefert die Formel das Datum des Montags dieser Kalenderwoche, gefolgt von einem Bindestrich und dem Datum des Sonntags der Kalenderwoche.
In der abgebildeten Arbeitsmappe liefert die folgende Formel in Zelle D4 als Ergebnis den Zeitraum „30.09.13 - 06.10.13“:
=TEXT(DATUM(C4;1;7*B4-3-WOCHENTAG
(DATUM(C4;;);3));“TT.MM.JJ“)&“ - „&TEXT
(DATUM(C4;1;7*B4+3-WOCHENTAG(DATUM(C4;;);3));
“TT.MM.JJ“)
Bei diesem Zeitraum handelt es sich um die Kalenderwoche 40 aus Zelle B4 im Jahr 2013 aus Zelle C4.
Das Ergebnis der Formel gibt einen Textinhalt aus, mit dem Sie nicht weiterrechnen können. Um den ausgegebenen Zeitraum zu verdeutlichen, können Sie das Ergebnis in einer Weise darstellen, dass die Wochentage mit ausgegeben werden. Dazu ändern Sie das Zahlenformat in den beiden TEXT-Funktionen der Formel folgendermaßen ab:
=TEXT(DATUM(C4;1;7*B4-3-WOCHENTAG
(DATUM(C4;;);3));“TTT TT.MM.
JJ“)&“ - “&TEXT(DATUM(C4;1;7*B4+3-
WOCHENTAG(DATUM(C4;;);3));“TTTTT.MM.JJ“)
Nach dem Kopieren der Formel in alle Zeilen der Tabelle werden die Datumsangaben grundsätzlich mit den Wochentagen ausgegeben.
Wie Sie mit Wochen in Excel rechnen und das richtige Datum ermitteln
In vielen Planungen spielen Wochen als Einheit eine Rolle, beispielsweise Kalenderwochen oder Produktionswochen. Wochen können in Excel ebenfalls mit eingängigen Formen kalkuliert und dargestellt werden.
Die folgende Abbildung zeigt eine Liste mit Datumswerten in Spalte A. In Spalte B befindet sich ein Wert, der anzeigt, um wie viele Wochen dieser Datumswert verschoben werden soll:
In Spalte C möchten Sie eine Formel verwenden, die auf der Basis des Datums aus Spalte A und der Wochenanzahl aus Spalte B den neuen Termin berechnet. Wenn die Wochenanzahl ein Wert größer als Null ist, wird das Datum in die Zukunft verschoben, bei einem negativen Wert in die Vergangenheit.
Dieses Ziel erreichen Sie, in dem Sie die Anzahl der Wochen mit dem Wert 7 multiplizieren und zum Ursprungsdatum addieren. Beim oben beschriebenen Tabellenaufbau sieht die Formel für Zelle C2 folgendermaßen aus:
=A1+7*B1
Die Formel ist anwendbar, da Microsoft Excel Datumswerte intern als Zahlen speichert. Der Abstand von einem Tag zu einem anderen beträgt 1. Die folgende Abbildung zeigt den Einsatz der Formel in einer Beispieltabelle:
Fazit: Kalenderwochen sind für ein professionelles Terminmanagement entscheidend
Termine bestimmen unser Leben sowohl im beruflichen und ebenso im privaten Umfeld. Bei der Planung von Terminen werden die Kalenderwochen mit fortlaufender Terminintensität wichtiger, da mit Ihnen mehr Übersichtlichkeit in der Terminplanung hergestellt werden kann.
Microsoft Excel ist in der Lage, zu jedem Datum die korrekte Kalenderwoche anzugeben. Entscheidend ist hierbei, dass Anwender in Europa beim Aufbau ihrer Excel-Formeln das europäische Datumssystem nach ISO 8601 beachten. Standardmäßig wird die Kalenderwoche nach dem US-Amerikanischen System berechnet, was zu Abweichungen führt. In den USA wird der erste Tag im Jahr grundsätzlich der Kalenderwoche 1 zugeordnet. In Deutschland, Österreich und der Schweiz ist als erste Kalenderwoche im Gegensatz generell als die Woche definiert, die als erste Woche im Jahr aus mindestens vier Tagen besteht. Das bedeutet für die Praxis, dass im europäischen System die KW 1 die Woche ist, die den ersten Donnerstag des Jahres umfasst.
Zur korrekten Berechnung der Kalenderwoche können Sie in Excel sowohl die Funktion KALENDERWOCHE und ebenso das Funktionsargument WOCHENSTART nutzen. Ebenfalls ist es möglich, individuelle Formeln aufzubauen und spezifisch auf Ihre Tabellenkalkulation anzupassen.
