Datumsberechnung Spezial (Teil 1) 12

Artikelbild-150
Wie man in Excel den letzten Montag (Dienstag, Mittwoch...) eines Monats berechnet.
 

Excel bietet standardmäßig schon eine ganze Menge an Datumsfunktionen. Trotzdem reichen die manchmal nicht aus. Denn was machst du, wenn du z.B. immer den letzten Montag eines Monats berechnen möchtest?

Eine etwas kniffelige Angelegenheit, für die es bestimmt unterschiedliche Lösungen gibt. Eine davon zeige ich dir im heutigen Artikel.

Und so geht’s:

Ich habe mir wieder eine kleine Beispieltabelle gebaut, die du bei Bedarf hier herunterladen kannst. In Spalte A ist für jeden Monat der Monatserste als Datum eingetragen, in Zelle B1 gebe ich den gesuchten Tag ein. Und in Spalte F soll mir Excel den dazu passenden letzten Tag im Monat berechnen:

Start und Ziel

Start und Ziel

Im Bild oben wäre das also der jeweils letzte Montag des Monats. Wie gehe ich dazu vor?

Zur besseren Nachvollziehbarkeit werde ich zunächst jeden Schritt in einer eigenen Hilfsspalte ausführen. Zum Schluss packen wir dann alles in eine schöne, kompakte Excel-Formel.

Schritt 1: Das Monatsende bestimmen

In Spalte A habe ich für jeden Monat den Monatsersten eingetragen, aber eigentlich brauchen wir den letzten Tag des Monats. Zum Glück gibt es dafür eine praktische Excel-Funktion, die wir in Spalte B schreiben:
=MONATSENDE(Ausgangsdatum; Monate)

Das Monatsende bestimmen

Das Monatsende bestimmen


Diese Funktion addiert zum Ausgangsdatum die angegebene Zahl von Monaten und berechnet dann den letzten Tag in dem Monat. Wenn man nun – so wie in unserem Fall – für den Parameter „Monat“ den Wert 0 (Null) angibt, dann berechnet Excel eben den letzten Tag im Monat des Ausgangsdatums.

Schritt 2: Der Wochentag des Monatsendes

Als nächstes möchte ich in Spalte C wissen, welchem Wochentag das Monatsende entspricht. Hier hilft die Funktion
=WOCHENTAG(Datum; Typ)

Über den Parameter „Typ“ definiert man, nach welchem Schema die Wochentage dargestellt werden sollen. Ich habe mich für den Typ 11 entschieden, das heißt der Montag entspricht der 1 und der Sonntag der 7:

Den Wochentag ausrechnen

Den Wochentag ausrechnen

Schritt 3: Der gesuchte Wochentag

Zusätzlich brauche ich in Spalte D den numerischen Wochentag für meinen in Zelle B1 eingetragenen gesuchten Tag. Da ich hier jedoch kein Datum verwendet habe, greife ich stattdessen über einen SVERWEIS auf eine kleine Umrechnungstabelle im Bereich A20:B26:

Der gesuchte Wochentag

Der gesuchte Wochentag

Jetzt trennt uns nur noch ein Schritt vom gewünschten Endergebnis.

Schritt 4: Die entscheidende Berechnung

Die alles entscheidende Frage lautet nun: Wieviele Tage muss ich vom Monatsende abziehen, damit ich auf den letzten Montag komme?

Der letzte Tag im Januar war ein Sonntag, folglich müssen 6 Tage abgezogen werden, um auf den letzten Montag zu kommen. Im Februar war der Monatsletzte bereits ein Montag, daher brauchen wir hier nichts abzuziehen. Im März fällt der Monatsletzte auf einen Donnerstag, somit müssen 3 Tage abgezogen werden usw.

Dafür setzen wir eine WENN-Abfrage ein:
=WENN(E5=D5;0;WENN(E5>D5;E5-D5-7;E5-D5))

Differenztage ermitteln

Differenztage ermitteln

Im Klartext heißt das:
Wenn der gesuchte Wochentag dem Wochentag des Monatsletzten entspricht, ist der Korrekturwert 0 (Null).
Wenn der gesuchte Wochentag nach dem Wochentag des Monatsletzten liegt, nehmen wir als Korrekturwert die Differenz der beiden und ziehen zusätzlich 1 Woche (= 7 Tage) ab.
Und ansonsten ist der Korrekturwert nur die Differenz der beiden Wochentage.

Schritt 5: Das Ergebnis steht fest

Zum Schluss addieren wir zum Monatsletzten aus Spalte B den eben ermittelten Korrekturwert aus Spalte E und haben damit unser gewünschtes Ergebnis:

Das Ergebnis steht fest

Das Ergebnis steht fest

Wenn ich nun den gesuchten Tag in Zelle B1 ändere, wird mir sofort das neue Ergebnis präsentiert:

Ein weiteres Beispiel

Ein weiteres Beispiel

Ach ja, fast hätte ich es noch vergessen. Natürlich geht das alles auch ohne die Hilfsspalten. Die Formel wird allerdings nicht ganz so kompakt, wie ich eingangs angedeutet habe:

Alles in einer einzigen Formel

Alles in einer einzigen Formel

In einem der nächsten Blog-Artikel werde ich darauf eingehen, wie man noch ein wenig mehr Flexibilität bekommt:
Dann berechnen wir solche Dinge wie den ersten Mittwoch im Monat, den zweiten Dienstag oder den dritten Freitag.

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

12 Gedanken zu “Datumsberechnung Spezial (Teil 1)

  • Avatar-Foto
    Carsten

    Hi,

    ich fand das eine spannende Aufgabe für den morgen und habe mich erstmal ungeachtet Deiner Lösung dran gesetzt:
    =WENN(WOCHENTAG(MONATSENDE(A2;0);2)=1;MONATSENDE(A2;0);MONATSENDE(A2;0)-(WOCHENTAG(MONATSENDE(A2;0);2)-1))

    Kommt auf das gleiche Ergebnis und ist deutlich kürzer! 😉

    Entscheidend ist der Teil -(WOCHENTAG(MONATSENDE(A2;0);2)-1)!

    Carsten

    • Avatar-Foto
      Martin Weiß

      Hallo Carsten,

      danke für die Formel und sie ist in der Tat deutlich kürzer 🙂
      Aber … ich habe sie bei mir gerade ausprobiert und für den Montag funktioniert sie ganz gut. Wenn man es aber mit einem anderen Wochentag versucht (die „1“ muss natürlich entsprechend angepasst werden), stimmt das Ergebnis nicht mehr bei allen Monaten. Oder habe ich etwas übersehen?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Carsten

    So, nachdem ich dann die ganze Aufgabe gelesen habe, habe ich meine Formel nochmal um die Flexibilisierung angepasst:
    =WENN(WOCHENTAG(MONATSENDE(A4;0);2)=SVERWEIS(s_WT;tage;2;0);0;SVERWEIS(s_WT;tage;2;0)-WOCHENTAG(MONATSENDE(A4;0);2)-WENN(WOCHENTAG(MONATSENDE(A4;0);2) Suche Wochentag, tage -> Zuordnung Tag – Nummer)

    • Avatar-Foto
      Carsten

      Da ist jetzt ein Teil meiner Antwort im Nirwana stecken geblieben.
      Die Formel:
      =WENN(WOCHENTAG(MONATSENDE(A4;0);2)=SVERWEIS(s_WT;tage;2;0);0;SVERWEIS(s_WT;tage;2;0)-WOCHENTAG(MONATSENDE(A4;0);2)-WENN(WOCHENTAG(MONATSENDE(A4;0);2) Suche Wochentag, tage -> Zuordnung Wochentag zu Nummer.

      • Avatar-Foto
        Carsten

        Interessant, bei der Kommentarfunktion bleibt etwas auf der Strecke…
        Zwischen der letzten Klammer und dem weitern Text „Suche Wochentag…“ fehlt noch, dass ich geschrieben, dass ich auf Zellen- und Tabellennamen zurückgegriffen habe.

  • Avatar-Foto
    Roland Wißler

    Hallo Martin,
    ich bin auch Excel Fan und User deiner Seite, jedoch kein Speziallist in Excel.
    Hier eine Frage:
    Kann ich eine ganze Zeile nach oben oder unten schieben wenn ein Bedingung erfüllt ist ?
    Vielleicht gibt es da auch eine Lösung die man finden kann.

    mit tabellarischen Grüßen Roland

    • Avatar-Foto
      Martin Weiß

      Hallo Roland,

      das Verschieben von Zellen auf Basis einer Bedingung lässt sich mit „normalen“ Mitteln nicht realisieren. Das geht nur mit Hilfe von VBA.

      Grüße,
      Martin

  • Avatar-Foto
    Klaus Müller

    K. Müller
    Hallo Carsten,
    Deine Datums Software ist toll, aber ich habe ein Problem,
    bei meinem Stunden/Arbeitszettel habe ich das Datum eine Verkettung
    das Jahr 2018 – 2020 [jedes Jahr, Monat, Tag] somit müsste ich das alles
    mit Ihrer Software, mein Problem Lösen können.
    Schöne Grüße
    Klaus

  • Avatar-Foto
    Ilian P

    Ähnlich wie mit den manuell berechneten Differenzen geht es auch mit der Formel „REST()“. So könnte man z.B. mit der folgenden Formel den letzten Freitag des Monats ausgeben:
    =DATUM(JAHR(A2);MONAT(A2)+1;0)+REST(-WOCHENTAG(DATUM(JAHR(A2);MONAT(A2)+1;0);2)-2;-7)

    • Avatar-Foto
      Martin Weiß

      Hallo Ilian,

      das ist ja wirklich eine sehr coole und kompakte Lösung, vielen Dank dafür!

      Für die Leser, die das nachspielen wollen: Für andere Wochentage muss nur der Wert angepasst werden, der an der vorletzten Stelle der Formel subtrahiert wird:
      Montag = 6
      Dienstag = 5
      Mittwoch = 4
      Donnerstag = 3
      Freitag = 2
      Samstag = 1
      Sonntag = 0

      Für den Montag lautet die Formel also:
      =DATUM(JAHR(A2);MONAT(A2)+1;0)+REST(-WOCHENTAG(DATUM(JAHR(A2);MONAT(A2)+1;0);2)-6;-7)

      Schöne Grüße,
      Martin