So berechnen Sie Kalenderwochen mit Excel fehlerfrei
Beispiel für falsche und richtige Angaben der Kalenderwochen.

So berechnen Sie Kalenderwochen mit Excel fehlerfrei

Für das Berechnen von Kalenderwochen Ihrer Datumsangaben besitzt Excel eine spezielle Tabellenfunktion mit dem Namen KALENDERWOCHE. Leider liefert diese Funktion in vielen Jahren falsche Ergebnisse. Umgehen Sie die Fehler mit einer passenden Formel!

Die Funktion KALENDERWOCHE berechnet die Kalenderwochen auch in den europäischen Versionen von Excel anhand des amerikanischen Standards. Der erste Tag im Jahr wird immer der Kalenderwoche 1 zugeordnet.

In Deutschland, Österreich und der Schweiz ist als erste Kalenderwoche aber immer diejenige Woche definiert, die als erste Woche im Jahr aus mindestens vier Tagen besteht. Wenn der 1. Januar also auf einen Freitag, Samstag oder Sonntag fällt, liefert die Tabellenfunktion KALENDERWOCHE ein falsches Ergebnis.

Wie Sie in der Abbildung oben in Spalte B erkennen können, berechnet Excel für das Jahr 2012 falsche Kalenderwochen. In Spalte C sehen Sie die korrekten Kalenderwochen.

Wie Sie die Kalenderwochen in Excel ermitteln

Die entsprechende Formel in Zelle C4 sieht so aus:

=KÜRZEN((A4-DATUM(JAHR(A4-REST(A4-2;7)+3);1;REST(A4-2;7)-9))/7)

Die Formel ist relativ komplex. Um die Funktionsweise verständlich zu erläutern, betrachten wir die einzelnen Bestandteile der Formel getrennt:

  • Innerhalb der Formel taucht mehrfach 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 liefert das den Rest 1, daher würde der Montag zu 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 Wochentagsberechnung 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 immer 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 stets ein negativer Tageswert. Daraus resultiert, dass das von DATUM berechnete Datum immer 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 nur ganzzahlige Werte zu erhalten.

Wie Sie den Anfang und das Ende einer beliebigen Kalenderwoche in Excel berechnen

Bei der Terminplanung spielen Kalenderwochen eine entscheidende Rolle. Häufig werden Termine für eine bestimmte Kalenderwoche geplant oder vereinbart. Dann müssen Sie nicht nur wissen, in welcher Kalenderwoche ein bestimmtes Datum liegt, sondern auch, an welchem Datum eine bestimmte Kalenderwoche beginnt oder endet.https://newopenx.simplethings.de/www/delivery/lg.php?bannerid=0&campaignid=0&zoneid=18&loc=https%3A%2F%2Fwww.computerwissen.de%2Foffice%2Fexcel%2Fartikel%2Fwie-sie-kalenderwochen-per-formel-berechnen.html&cb=1f8a22410a

Diese Information hilft Ihnen bei der Planung von Fertigungsterminen 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.

Beim Ergebnis der Formel handelt es sich um einen Textinhalt, mit dem Sie nicht weiterrechnen können. Um den ausgegebenen Zeitraum zu verdeutlichen, können Sie das Ergebnis so 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 alle Datumsangaben 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. Aber wie rechnen Sie mit Wochen in Excel?

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.

Das 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 funktioniert, weil 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: