Der erste Tag einer Kalenderwoche 7

Eine trivial erscheinende Frage erweist sich als harte Nuss!
 

Die Kalenderwoche ist gerade im Geschäftsbereich eine gängige Zeitangabe. Vor allem bei Projekten arbeitet man häufig mit Kalenderwochen, um den Beginn oder die Dauer von Projektschritten zu definieren.

Excel bietet hier mit der KALENDERWOCHE-Funktion ein praktisches Mittel, um aus einem Datum die zugehörige Kalenderwoche zu ermitteln. Was mache ich aber für den umgekehrten Fall: Wenn ich nur eine Kalenderwoche gegeben habe und dazu z.B. das Datum des ersten Tages wissen möchte?

Kann doch gar nicht so schwer sein, habe ich mir gedacht. Bis mir nach einiger Zeit ziemlich der Kopf geraucht hat, denn die Tücke steckt hier im Detail.

Schließlich habe ich doch noch eine Lösung gefunden, welche sogar die in unseren Breiten übliche ISO-Kalenderwochenregelung berücksichtigt.

Und so geht’s:

Im Internet schwirren einige Lösungen zu dieser Aufgabe herum. Allerdings haben sie alle (zumindest diejenigen, die ich finden konnte) einen großen Haken: Sie berücksichtigen nicht die ISO-konforme Kalenderwochenberechnung.

Die Tücken der Kalenderwoche

Auch wenn es dem Laien meist nicht bewußt ist: Die Berechnung einer Kalenderwoche ist nicht ganz ohne. Im Artikel “Wie Dir Excel zu 2 Wochen mehr Urlaub verhilft” habe ich sehr plastisch gezeigt, dass die Excel-Funktion KALENDERWOCHE etwas mit Vorsicht zu genießen ist.

Laut ISO 8601 ist die erste Kalenderwoche eines Jahres diejenige, in der der erste Januar-Donnerstag liegt. Infolgedessen kann es passieren, dass der 01.01. nicht in der KW 1 liegt, sondern in der KW 52 oder 53 des Vorjahres (so der Fall für den 01.01.2012 oder den 01.01.2016).

Um das zu berücksichtigen, sollte man in der Funktion KALENDERWOCHE grundsätzlich für den zweiten Parameter den Zahl-Typ 21 angeben. Im folgenden Bild sieht man ganz gut, wann der 1. Januar auch tatsächlich in KW 1 liegt. Nämlich immer dann, wenn er vor oder auf den ersten Donnerstag des Jahres fällt:

KALENDERWOCHE-Funktion

KALENDERWOCHE-Funktion

Auf diese Hintergrundinformation greifen wir im Verlauf des Artikels noch zurück. Kommen wir zunächst zu den Wochentagen.

Die Funktion WOCHENTAG

Mit dieser Funktion lässt sich die fortlaufende Nummer des Wochentages eines bestimmten Datums ausgeben.
=WOCHENTAG(Datum; Typ)

Für den Typ stehen folgende Optionen zur Verfügung:

Zahl-Typen

Zahl-Typen


In unseren weiteren Berechnungen verwende ich den Typ 11 und erhalte damit die Werte 1 für Montag bis 7 für Sonntag.

Die Funktion DATUM

Als letzte Funktion brauchen wir noch die DATUM-Funktion. Diese liefert den Datumswert zurück, wenn man als Parameter Jahr, Monat und Tag übergibt:
=DATUM(Jahr;Monat;Tag)

Somit haben wir alle Hilfsmittel beisammen, um uns langsam der Ursprungsaufgabe zu nähern: Welche Tage gehören zu einer gegebenen Kalenderwoche?

Die Aufgabe

Wir geben also das gewünschte Jahr und die Kalenderwoche an und wollen dazu die zugehörigen Tage berechnen lassen, wie im folgenden Bild dargestellt:

Die Aufgabe

Die Aufgabe

Bevor wir uns an die eigentliche Formel machen, stellen wir noch ein paar generelle Überlegungen an:

  • Eine Kalenderwoche beginnt am Montag, was also Tag 1 der jeweiligen Woche sein soll
  • Jede Woche hat 7 Tage
  • Das Jahr beginnt mit KW 1
  • Der 1. Januar kann, muss aber nicht zwingend in KW 1 liegen

Wenn wir also den ersten Montag des Jahres bestimmen könnten, dann bräuchten wir nur noch die Anzahl der Wochen multipliziert mit 7 Tagen zu addieren, um auf den Montag in unserer gesuchten Kalenderwochen zu kommen. In einem Flußdiagramm sehen diese Überlegungen folgendermaßen aus:

Der Ablauf als Diagramm

Der Ablauf als Diagramm

Und jetzt müssen wir das Ganze nur noch in die entsprechende Excel-Formel gießen. Dazu kombinieren wir die zu Beginn erläuterten Funktionen KALENDERWOCHE, DATUM und WOCHENTAG. Heraus kommt dabei folgendes “Monster”:

Die fertige Formel

Die fertige Formel

Keine Angst, wir zerlegen die Formel nochmal in die Bestandteile. Dann wird das Bild hoffentlich ein wenig klarer:

Die Monster-Formel erläutert

Die Monster-Formel erläutert

Damit haben wir den ersten Tag unserer vorgegebenen Kalenderwoche. Bei Bedarf erhalten wir die restlichen Tage, indem wir einfach noch den Wert 1 zum jeweiligen Vortag addieren:

Die restlichen Tage bestimmen

Die restlichen Tage bestimmen

Zur Kontrolle lassen wir uns mit der KALENDERWOCHE-Funktion noch bestätigen, dass das ermittelte Datum auch wirklich in der angegebenen Woche liegt:

Kontrolle ist besser

Kontrolle ist besser

Passt! Und funktioniert ganz ISO-konform auch für Jahre, bei denen der 1. Januar nicht auf die KW 1 fällt:

Alles ISO-konform

Alles ISO-konform

Ich gebe zu, dass das eine etwas schwerere Geburt war. Aber wenn’s leicht wäre, könnte es ja jeder 🙂

Kennst Du vielleicht doch noch einen einfacheren Weg, um dieses Problem zu lösen? Dann lass es uns unten in den Kommentaren wissen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Der erste Tag einer Kalenderwoche

  • Timo

    Klasse!!
    Hab ich mich immer mit rumgeägert. Danke für die Lösung.
    Beim Abtippen der “Monster”-Formel passieren leicht Fehler wegen den Klammern und Semikolons. Hier für diejenigen die sich das Abtippen ersparen wollen (oder habe ich nur den Download übersehen?):

    =WENN(KALENDERWOCHE(DATUM(A3;1;1);21)=1;WENN(WOCHENTAG(DATUM(A3;1;1);11)=1;DATUM(A3;1;1)+7*(B3-1);DATUM(A3;1;1)-WOCHENTAG(DATUM(A3;1;1);11)+1+7*(B3-1));WENN(WOCHENTAG(DATUM(A3;1;1);11)=1;DATUM(A3;1;1)+7+7*(B3-1);DATUM(A3;1;1)+7-WOCHENTAG(DATUM(A3;1;1);11)+1+7*(B3-1)))

    Weiter so!
    Freue mich schon auf die nächsten Tipps und Tricks.

    Timo

    • Martin Weiß Beitragsautor

      Hallo Timo,

      nein, Du hast nichts übersehen, es gibt tatsächlich keinen Download. Aber Du hast Recht: Bei so einer Formel sind Tippfehler vorprogrammiert. Danke für’s Reinstellen.

      Schöne Grüße,
      Martin

    • Martin Weiß Beitragsautor

      Hallo Luschi,

      vielen Dank für den Link. Ich hätte mich auch gewundert, wenn es nicht noch andere Lösungen gibt. Wenngleich ich auch zugeben muss, die genannten Formeln noch nicht alle ganz durchdrungen zu haben 🙂

      Aber sie funktionieren, und das ist die Hauptsache!

      Schöne Grüße,
      Martin

    • Martin Weiß Beitragsautor

      Hallo Stephen,

      meines Wissens gibt es keinen Unterschied. Ebenso bei Typ 1 und Typ 17, auch die scheinen identisch zu sein.

      Schöne Grüße,
      Martin