Wer selbstaktualisierende Berichte erstellen wird, ist meist auch damit konfrontiert wie er Datum, z.B. Monatsende oder den Ersten eines Monats, automatisiert erstellen kann.

Excel bietet uns zwei Möglichkeiten an ein Monatsende zu kalkulieren:

1. Mit der MONATSENDE() Formel
2. Mit der DATUM() Formel und einem kleinen Trick

Mit der 2. Variante kann man aber auch den Ersten eines Monats ausfindig machen!

In diesem Beitrag erkläre ich die beiden Funktionen.

Variante 1: um den letzten Tag zu berechnen MONATSENDE()

Um den letzten Tag im Monat zu berechnen hat Excel speziell eine Formel namens MONATSENDE(). Diese war bis an hin im Analyse-Funktionen Add-In vorhanden, weshalb das Add-In zuerst aktiviert werden musste um sie zu benutzen. Seit Excel 2013 scheint diese aber bereits im Standard Formelsortiment vorhanden zu sein.

MONATSENDE() Syntax Erklärt:

Die Syntax der Formel sieht wie folgt aus:
=Monatsende(Ausgangsdatum; Monate)

Das Ausgangsdatum Argument ist wie der Name sagt, das Ausgangsdatum anhand dessen der letzte Tag im Monat kalkuliert wird. Hier handelt es sich in der Praxis meist um das aktuelle Datum, oder das gewünschte Startdatums eines Projektes, für das den letzten Tag eines zukünftigen Monats kalkuliert werden soll.

Das Monate Argument ist die Anzahl Monate die vor oder nach dem Ausgangsdatum liegen. Der Monat in dem das Ausgangsdatum liegt ist hier immer 0! Es können positive wie auch negative Werte benutzt werden.

Das aktuelle Beispiel =MONATSENDE(“29.01.2015”; 0) gibt den letzten Tag im Januar 2015 zurück: 31.01.2015

Monatsende in der Zukunft berechnen

In dem wir Monate auf z.B. 3 setzen, sagen wir der Formel das wir den letzten Tag vom Monat wollen der 3 Monate in der Zukunft liegt.

Nochmals: Jan = 0, Feb = 1, Mrz = 2, Apr =3

Die Formel =MONATSENDE(“29.01.2015”; 3) gibt uns 30.04.2015 zurück.

Monatsende in der Vergangenheit berechnen

Dasselbe geht auch für vergangene Monate. Jetzt zählen wir einfach rückwärts.

=MONATSENDE(“29.01.2015”; -2)

Nov = -2, Dez = -1, Jan = 0

Womit wir den 30.11.2014 zurückgegeben bekommen von Excel.

Variante 2: DATUM() damit man den letzen oder ersten Tag im Monat berechnen kann

Mit DATUM können wir das gleiche erreichen wie MONATSENDE, und sogar mit weniger Aufwand, gerade wenn wir einen Bericht erstellen in dem wir mehrere Monatsenden abbilden müssen.

MONAT() ist eine Standard Datum & Uhrzeit Funktion, und wird benutzt um per Formel oder manuell ein Datum zu erstellen.

MONAT() Syntax Erklärt:

Die Syntax der DATUM Formel sieht wie folgt aus:
=DATUM(Jahr; Monat; Tag)

Die Argumente in dieser Formel sind selbst sprechend: Das Argument Jahr wird benutzt um das Jahr in dem zu erstellenden Datum festzulegen. Monat wird benutz um den Monat, und Tag um den Tag festzulegen.

Beispiel Formel mit DATUM()

=DATUM(2015; ZEILE(A1)+1; 0)

In diesem Beispiel ist das Jahr als Konstante 2015 gesetzt. Damit wir nicht jede Monatszahl von Hand eingeben müssen wenn wir die Formeln nach unten kopieren, habe ich die Formel ZEILE() benutzt und auf die Zelle A1 verlinkt.

Den ersten Tag eines Monats berechnen

Würden wir jetzt =DATUM(2015; Zeile(A1); 1) eingeben, so würde Excel dies als den 01.01.2015 auswerten.

Den ersten eines Monats per Formel zu erstellen ist also relativ einfach.

Den Letzten Tag eines Monats berechnen

Den letzten Tag eines Monats zu berechnen ist genauso einfach. Hierfür muss man lediglich verstehen wie Excel die Datum berechnet.

Will man den ersten Tag im Monat berechnen mit DATUM so trägt man beim Argument Tag eine 1 ein. Möchte man den 2. so ist es eine 2. Etc.

Excel versteht die Tage als Zahlenstrahl. Ein Zahlenstrahl kann aber nicht nur aufsteigend sein, sondern auch absteigend. Deshalb können wir auch 0 oder -1, etc. eintragen.

Excel versteht den 0. Tag eines Monats dann als den Tag der vor dem 1. Monatstag kommt. Oder anders ausgedrückt, den letzten Tag im Vormonat! Dies können wir uns zu nutzen machen.

Wollen wir den letzten Tag im Januar berechnen würde die Formel so aussehen:

=DATUM(2015; 2; 0)

Dies ergibt dann den 31.01.2015.

Wenn wir die Formel jetzt noch dynamisch erstellen wollen sieht sie so aus:

Wenn Ihr diese Formel in einem neuen Arbeitsblatt in irgend eine Zelle kopiert und nach unten kopiert wird es Euch für das Jahr 2015 alle Monatsende berechnen!

Randnotiz:

In China verbietet die kommunistische Partei das Erwähnen des Jahrestages des Tian’anmen-Massaker (4. Juni 1989) auf dem Internet. Die erfinderischen Chinesen benutzten deshalb eine weile lang einen Code wenn sie darüber in Blogs schrieben: den 35. Juni.

Excel wertet dieses Datum natürlich richtig aus :- )

Probiert es mal: =DATUM(1989;5;35)