Excel Funktion =Monatsende()

Die Funktion =MONATSENDE() nutzen wir, damit uns der letzte Tag eines Monats angezeigt wird. Wir benötigen diese Funktion, wenn wir Fälligkeits- oder Rückzahlungstermine berechnen wollen. Da hier aber nicht jeder mit solchen Terminen arbeitet, werde ich euch ein anderes Beispiel zeigen.

Fangen wir mit dem Aufbau der Funktion an. Die Syntax dazu lautet:

 

=MONATSENDE(Ausgangsdatum;Monate)

 

Sieht gar nicht schwer aus und ist es auch nicht. Das Ausgangsdatum ist, wie das Wort schon ausdrückt, dass Datum von dem wir ausgehen. Da ist es egal, ob wir vom heutigen Datum ausgehen, einem Datum in der Zukunft oder eines was in der Vergangenheit liegt. Das Datum kann am Anfang eines Monats sein, in der Mitte oder am Ende.

 

Die Variable Monate ist auch nicht schwer zu verstehen. Wir schreiben eine 0 für den Monat, welcher in unserem Datum steht, eine 1 für den nächsten Monat, eine 2 für den darauf folgenden Monat usw. Wollen wir hingegen auf ein Datum in der Vergangenheit zugreifen, so setzen wir einfach ein „-“ vor die Monatszahl. Also eine -1 für den vorigen Monat, eine -2 …, etc.

Ihr werdet mit dieser Formel - auch für den Februar - immer den letzten Tag des Monats angezeigt bekommen. Selbst ein Schaltjahr erkennt Excel richtig, probiert es aus!

Auch für den Februar? Das ist ein schöner Übergang zu dem Beispiel, welches ich euch für die Funktion Monatsende liefern wollte.

 

 Bauen wir uns also einen Kalender, der abgeleitet aus der jeweiligen Jahreszahl, immer stimmen sollte.Für alle Monate eines Jahres ist es relativ einfach, denn da ist deren Anzahl an Tagen eindeutig geregelt, nur der Februar macht uns da einen Strich durch die Rechnung. Drei Jahre lang hat er 28 und im vierten Jahr 29 Tage – aber stimmt unsere Annahme? Wenn ich ehrlich bin: NEIN.  Papst Gregor XIII.  (1572) hat sich bei seinem Kalenderaufbau etwas Besonderes einfallen lassen. Drei so genannten Gemeinjahren mit 28 Tagen im Februar folgt ein Schaltjahr mit 29 Tagen im Februar (dem Schalttag) mit der Ausnahme, dass bei einem vollen Jahrhundert der Schalttag ausfällt, es sei denn es ist durch 400 teilbar.Leider hat die in Excel integriert Kalenderfunktion einen Fehler: Sie berücksichtigt diese Besonderheit nicht! Deshalb gibt es in Excel den 29.02.1900, obwohl 1900 geteilt durch 400 keine Ganzzahl ergibt!Nun wieder zurück zu unserem Kalender, der diese Besonderheit berücksichtigen soll. Die Einrichtung der 12 Monate, außer unserem Februar, dürfte keine Schwierigkeit bereiten. In jedem Monat den 01. eintragen und in den Folgezellen immer 1 zur vorangegangenen hinzuzählen: A2 = A1+1. Würden wir uns hier nichts einfallen lassen, so bekämen wir in Schaltjahren den 29. Februar und in den Gemeinjahren bereit an der gleichen Stelle den 01. März angezeigt.

 

excel formel monatsende 01

 

 

Das so etwas nicht in unserem Sinn sein kann, dass ist wohl klar. Wir gestalten uns deshalb eine einfache Wenn - Formel, welche das Monatsende abfragt:

 

=WENN(B30=MONATSENDE(B30;0);"";B30+1)

 

Diese Formel prüft ob das Datum in der Zelle B30 bereits der letzte Tag des Monats ist. Wenn ja, dann soll in Zelle B31 nichts mehr eingetragen werden. Ist das Monatsende aber noch nicht erreicht, dann soll auf das Datum aus Zelle B30 noch ein Tag aufaddiert werden.

 

excel formel monatsende 03

excel formel monatsende 02

 

So weit so gut – nun müssen wir noch die Jahrhundert-Regel einbauen. Dazu prüfen wir mit dem Vergleich JAHR(B30)/400=GANZAHL(JAHR(B30)/100), ob der Schalttag aktuell ist oder nicht. Ist der Vergleich WAHR, dann gibt es den 29. Februar. Auf ein volles Jahrhundert brauchen wir nicht zu prüfen, denn eine unterjährige Division ergibt immer ein gebrochenes Ergebnis.Bauen wir unsere Überlegung nun noch in die bereits erstellte Formel ein, so lautet sie dann so:

 

=WENN(B30<>MONATSENDE(B30;0);B30+1;WENN(JAHR(B30)/400=GANZZAHL(JAHR(B30)/100);B30+1;""))

 

Warum das Vergleichszeichen im ersten Vergleich zwischen B30 und MONATSENDE(B30;0) sich von „=“ (gleich) nach „<>“ („ungleich“) verändert, dürft ihr selbst herausbekommen!  Hinweis: Sollte die Formel bei Euch einen Fehler ausgeben, so müssen die Analyse Funktionen eingeschaltet werden!

Cookies erleichtern die Bereitstellung unserer Dienste. Mit der Nutzung unserer Dienste erklären Sie sich damit einverstanden, dass wir Cookies verwenden.
Weitere Informationen Ok