Der erste Tag einer Kalenderwoche 36

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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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.



Schreibe einen Kommentar zu Konrad Schierer Antworten abbrechen

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

36 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ß Autor des Beitrags

      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ß Autor des Beitrags

      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ß Autor des Beitrags

      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

    • Martin Weiß Autor des Beitrags

      Hallo Hilfsscherrif,

      die Funktion KALENDERWOCHE liefert die Kalenderwoche des Datums, nicht jedoch das Datum des ersten Tags in der betreffenden Woche. Oder habe ich irgendetwas überlesen?

      Schöne Grüße,
      Martin

  • Markus

    Hallo Martin,

    danke, für die tolle Formel.
    Ich möchte gerade einen Wochenkalender (Mo. bis So.) damit erstellen. nun habe ich das Problem, das z.B.im Januar 2017 der erste an einem Sonntag ist und nicht angezeigt wird.
    Wie kann ich in Excel dieses Problem umsetzen, dass alle Tage vor dem ersten Montag angezeigt werden oder bin ich total auf dem Holzweg, dass ich den Wochenkalender mit dieser Formel nicht erstellen kann.

    Viele Grüße
    Markus

    • Martin Weiß Autor des Beitrags

      Hallo Markus,

      ich denke, hier musst Du ein wenig mit der WENN-Funktion herumspielen, um solche Fälle abzufangen. Pauschal lässt sich das nicht sagen, da es vom genauen Aufbau der Tabelle und von Deinen Formeln abhängt.

      Schöne Grüße,
      Martin

  • Ansgar Vollmeyer

    Hallo Martin,
    deine Excel-Beiträge finde ich großartig. Was hältst du von folgender Variante (mit OpenOffice erstellt, sollte mit Excel aber auch funktionieren)?:

    =(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7

    oder mit Fehlermeldung:
    =WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7;21)B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7)
    Grüße von Ansgar

  • Ansgar Vollmeyer

    hier noch eine Ergänzung: vor dem: …B3;“Fehler“;… muss das Ungleichzeichen „“ (ohne Anführungsstriche) eingefügt werden (das wurde leider beim Veröffentlichen rausgelöscht.

    „=WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7;21)<>B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7)“

    Ansgar.

    • Martin Weiß Autor des Beitrags

      Hallo Ansgar,

      vielen Dank für diese schöne Formel! Die funktioniert auch mit Excel. Kleine Änderung: Man muss zum Schluss den Wert 1 addieren, damit der Montag dabei herauskommt. Ansonsten wird immer der Sonntag ausgespuckt. Hier also nochmal die beiden angepassten Versionen:

      =(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1
      bzw.
      =WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1;21)<>B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1)

      In jedem Fall um Längen eleganter als das von mir im Artikel vorgestellte Monster 😉

      Danke dafür,
      Martin

    • Bernard Miserez

      Auch ich bedanke mich herzlich für diese Lösung. Ich bedanke mich aber auch bei allen, die zu diesem Thema geschrieben haben!
      Bernard

    • Kevin

      Hallo Rene,

      wirklich super kurz und funktioniert wie ich’s brauche. Aber verstanden hab ichs nicht.
      Du suchst ein Datum. Dieses Datum ist im Jahr N1 im ersten Monat und im x-ten Tag (Bei mir wars die KW 28 in 2019 und damit der 189. Tag in 2019). Auf so eine Idee wär ich gar nicht gekommen, ist aber genial.
      Dieser 189. Tag berechnet sich aus 7 * die Anzahl der Kalenderwochen (im Beispiel also 7 * 28 = 196) abzüglich etwas.
      Und genau dieses Etwas verstehe ich jetzt nicht. Was wird hier abgezogen? -3 (warum?) und dann ein bestimmter Wochentag???
      Könntest du das mir zum Verständnis erklären?

      Wie gesagt die Lösung ist super kurz und funktioniert tadellos, dafür schon mal meinen Respekt und Dank.

      Grüße,
      Kevin

    • Karas

      Lieber Rene,

      kannst du die Formel vielleicht nochmal genauer erklären? Oder gerne auch du @Martin Weiß 🙂

      Ich häng mich ein wenig dran auf, warum -3 und ein unbestimmter Wochentag (DATUM(N1;;);3) subtrahiert werden.

      LG
      Karas

      • Martin Weiß Autor des Beitrags

        Hallo Karas,

        da kann ich leider nicht viel beitragen, denn mir erschließt sich die Formel auch nur teilweise.

        Der unbestimmte Wochentag (DATUM(N1;;);3) ergibt immer den Wochentag des 30.11. des jeweiligen Vorjahres. Man könnte auch schreiben DATUM(N1;0;0). Der nullte Monat ist in der Excel-Logik der Dezember (da der erste Monat der Januar ist). Der nullte Tag ist immer der letzte Tag des Vormonats. Und daraus ergibt sich der 30.11., also der letzte Tag vor dem Dezember.

        Die WOCHENTAG-Funktion mit dem Parameter 3 liefert den laufenden Wochentag beginnend mit Montag = 0, Dienstag = 1, … Sonntag = 6.
        Unter der Annahme, dass das gegebene Jahr in Zelle N1 = 2015, dann liefert die WOCHENTAG-Funktion Folgendes:
        =WOCHENTAG(DATUM(N1;;);3) =
        =WOCHENTAG(„30.11.2014“);3) = 6
        da der 30.11.2014 auf einen Sonntag fällt.

        Aber dann beißt’s ehrlich gesagt auch bei mir aus…

        • Karas

          Lieber Martin,

          vielen Dank schon einmal für die Erklärung! Ich bezweifle, dass Rene hier nochmal reinschaut und seine Formel auflöst :/ Die funktioniert ja auch eigentlich sehr gut, leider gibt es für die KW01 immer falsche Werte an. Hätte daher gerne mal gewusst was er sich dabei gedacht hat.

          LG
          Karas

          • M.T-S.

            Hallo Martin,
            Hallo Karas,

            Der =DATUM(N1;1;7*1-3) ergibt immer den 04. Januar des laufenden Jahres.
            Die erste Kalenderwoche definiert sich dadurch, das der erste Donnerstag eines Jahres immer in der ersten Kalenderwoche liegt. Dadurch ergibt sich auch, dass der 04. Januar immer in der ersten KW liegen muss.
            Davon ausgehend wird dann die KW multipliziert. Also KW 1 = 7*1-3 = 04 | KW 2 = 7*2-3 = 11 (siehe 11 = 04 + 7).
            Allerdings wird mir nicht ganz klar wieso der letzte (Wochen-)Tag, 2 Monate zuvor abgezogen wird, könnte mir aber vorstellen, dass evtl. das Schaltjahr des Vorjahres (wenn es eins ist) eine Rolle spielen könnte.
            Wenn es das ist, ist es ein kleiner Denkanstoß 🙂

            Gruß Marcel

          • Martin Weiß Autor des Beitrags

            Hallo Marcel,

            danke schon mal für die Ergänzungen, die Regel mit dem 4. Januar bzw. dem Donnerstag ist soweit grundsätzlich auch klar. Auch wenn sich die gesamte Logik hinter dieser schönen kurzen Formel von Rene sich mir immer noch nicht erschließt…

            Schöne Grüße,
            Martin

          • M.T-S.

            @Karas

            [„Die funktioniert ja auch eigentlich sehr gut, leider gibt es für die KW01 immer falsche Werte an.“]

            Bei mir passt das mit der ersten KW und dem ersten Tag. Zumindest vom Jahr 1901 an.
            Das Jahr 1900 gibt mir einen #ZAHL! Fehler. Und von Jahr 1 bis 1899 sind die Werte „Falsch“, was allerdings daran liegt, das Excel einfach die Jahre nicht kennt. Der Excel Kalender fängt am 01.01.1900 mit der Zahl 1 an zu rechnen.

            Gruß M.T-S,

          • M.T-S.

            Hallo Martin,
            hallo Karas,

            ich habe ein wenig mit der Formel herumgespielt, und hab diese soweit (denke ich) aufgedröselt.

            =DATUM(N1;1;7*M1-3) ergibt für die erste KW immer den 04.01. und pro KW werden 7 Tage addiert.
            Der 04.01. liegt IMMER in der ersten KW und darüber lässt sich der Wochentag über gleichnamige Funktion bestimmen.
            Der ist eigentlich auch wichtig um den ersten Tag der ersten KW zu bestimmen und darüber dann alle weiteren Tage der jeweiligen KW. Einfach immer 7 Tage addieren also + (7 * KW) oder 7*M1.
            Der Wochentag für den 04.01. ist tatsächlich auch in der Formel enthalten und zwar hier.
            WOCHENTAG(DATUM(N1;;);3)
            Das ergibt, wie du schon erläutert hast Martin, den letzten Tag vom Vorletzten Monat.
            Wenn man nun den 30.11. des Vorjahres, vom 04.01. des aktuellen Jahres abzieht, ergibt das 35 Tage.
            35 ist glatt durch 7 Teilbar. Das würde 5 ergeben, interessiert hier aber überhaupt nicht. Wichtig ist, das es durch 7 Teilbar ist ohne Rest, denn dadurch ist der Wochentag vom 04.01. genau der selbe wie der vom 30.11. des Vorjahres.
            Das bedeutet dann, man könnte die Formel auch so schreiben:
            =DATUM(N1;1;7*M1-3-WOCHENTAG(DATUM(N1;1;4);3))

            Also Zusammenfassend:
            Der erste Teil berechnet den 04.01. und addiert 7 Tage pro Kalenderwoche dazu (verschiebt also das Datum zur gesuchten Kalenderwoche).
            Der zweite Teil zieht den Wochentag des 04.01. (bzw. des 30.11.) vom ermittelten Datum ab.
            Dadurch erhält man das Datum des Montags der gesuchten KW.

            So ich hoffe, dass ich das nun nicht zu kompliziert erklärt habe. 🙂

            Gruß Marcel

  • Yannick

    =MAX(
    KALENDERWOCHE(DATUM($D$43;12;26);21);
    KALENDERWOCHE(DATUM($D$43;12;27);21);
    KALENDERWOCHE(DATUM($D$43;12;28);21);
    KALENDERWOCHE(DATUM($D$43;12;29);21);
    KALENDERWOCHE(DATUM($D$43;12;30);21);
    KALENDERWOCHE(DATUM($D$43;12;31);21))

    Wie siehts damit aus?

    • Martin Weiß Autor des Beitrags

      Hallo Yannick,

      die Formel liefert auf jeden Fall immer die letzte Kalenderwoche des angegebenen Jahres, was schon mal ein guter Ansatz ist. Aber leider nicht den gesuchten ersten Tag zu einer gegebenen Kalenderwoche.

      Schöne Grüße,
      Martin

  • Harald Manthey

    Formel: Neujahr – (WOCHENTAG(Neujahr;11)-1)
    Damit ermittel ich den Montag der Neujahrswoche. Alles weitere ist reine Addition/Multiplikation.

    Grüße aus Berlin
    Harald

  • Martin Auer

    Hallo zusammen, sehr viele gute Inputs.
    Ich möchte zur KW eine Datums aber auch gerne das korrekte Jahr in der Form [JJJJ]/KW[KW] ausgeben (zb 2004/KW53).

    Dazu verwende ich folgende Formel (Datum ist in A2):
    =JAHR((„4.1.“&JAHR(A2))+KALENDERWOCHE(A2;21)*7-4-REST(„2.1.“&JAHR(A2);7))&“/KW“&TEXT(KALENDERWOCHE(A2;21);“00“)

    Funktioniert auch prima, allerdings hat die Formel anscheinend ein Problem bei Jahren mit KW53 (zB 2004)
    Di, 28.12.2004: 2004/KW53
    Mi, 29.12.2004: 2004/KW53
    Do, 30.12.2004: 2004/KW53
    Fr, 31.12.2004: 2004/KW53
    Sa, 01.01.2005: 2006/KW53 => Jahr sollte 2004 sein
    So, 02.01.2005: 2006/KW53 => Jahr sollte 2004 sein
    Mo, 03.01.2005: 2005/KW01
    Di, 04.01.2005: 2005/KW01

    Ich komm leider auf meinen Denkfehler nicht drauf.
    lg
    Martin

    • Martin Weiß Autor des Beitrags

      Hallo Martin,

      tut mir leid, da muss ich auch passen. Vielleicht hat ein anderer Leser eine Idee.

      Schöne Grüße,
      Martin

      • Martin Auer

        Hallo Martin,
        Habs hinbekommen (Tips aus Herbers Excel Forum waren ausschlaggebend)
        HIer die Formel (Datum in A2):
        =VERKETTEN(„KW „;MIN(JAHR(A2-1-REST(A2-2;7)+4);JAHR(A2-REST(A2-1;7)+4));“_“;TEXT(KÜRZEN((A2-DATUM(JAHR(A2+3-REST(A2-2;7));1;REST(A2-2;7)-9))/7);“00″))

        Wobei der Term
        ==> MIN(JAHR(A2-1-REST(A2-2;7)+4);JAHR(A2-REST(A2-1;7)+4))
        das korrekte Jahr berechnet und der Term
        ==> TEXT(KÜRZEN((A2-DATUM(JAHR(A2+3-REST(A2-2;7));1;REST(A2-2;7)-9))/7);“00″)
        die korrekte KW berechnet.

        Ergebnis zB
        Datum JJJJ+KW
        Do, 30.12.2004 KW 2004_53
        Fr, 31.12.2004 KW 2004_53
        Sa, 01.01.2005 KW 2004_53
        So, 02.01.2005 KW 2004_53
        Mo, 03.01.2005 KW 2005_01
        Di, 04.01.2005 KW 2005_01

        • Martin Weiß Autor des Beitrags

          Hallo Martin,

          wow, diese Formel ist ein echtes „Schmankerl“! Danke fürs Teilen.

          Schöne Grüße,
          Martin

  • viola Vockrodt-Scholz

    Das finde ich wirklich beeindruckend. Allerdings denke ich, dass die Wahl der Kalenderwoche System 2 doch bereits die Prüfung auf die Länge der 1. KW im Jahr enthält. Deshalb prüfe ich einfacher:
    =WENN(WOCHENTAG($A4;2)=1;KALENDERWOCHE($A4;21);““)

    m.E. stimmt das so.