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
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
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
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
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
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
Wenn ich nun den gesuchten Tag in Zelle B1 ändere, wird mir sofort das neue Ergebnis präsentiert:
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
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.
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.
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
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
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)
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.
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.
Toller Tipp. Vielen Dank. Die Funktion mit dem Monatsletzten kannte ich noch gar nicht..
Das ist das Schöne an Excel: Man lernt nie aus. Mir geht es auch oft so.
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
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
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
Ä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)
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