Excel und das Monatsende
Excel kennt das Datum und kann sogar besser damit umgehen, als man erst meint.
Monatsultimo kompliziert oder einfach?
Wenn Sie den Blogtext Excel und Treppensteigen gelesen haben, dann wird Ihnen womöglich unter „Kniff zum Monatsultimo“ ein Satz aufgefallen sein: „Die Berechnung des letzten Tages eines Monats …. ist aber ziemlich kompliziert, …“ Hier kann ich mit einem eindeutigen „JEIN“ antworten: Man kann es sich kompliziert oder einfach machen.
Formel zur Berechnung des Monatsultimos (kompliziert)
Für ein beliebiges Datum (hier in Feld A2) ermittelt man mit der MONAT-Funktion den jeweiligen Monat (Zeile 1) und mit der JAHR-Funktion das Jahr (Zeile 2). Mit der WAHL-Funktion legen Sie folgendes fest: Wenn der Monat 1 ist, dann liefert die Funktion 31, bei 2 liefert sie 28 usw. Für das Jahr 2016 (Schaltjahr) ist das für die Zeile 3 errechnete Ergebnis natürlich falsch. Daher muss anstelle der fest eingefügten „28“ eine weitere Formel für den Februar erstellt werden: Wenn sich die Jahreszahl ohne Rest durch 4 teilen läßt (Kriterium für das Schaltjahr), dann hat der Februar 29 Tage, ansonsten 28 (Zeile 4). Die Formel aus Zeile 4 wird in die Formel aus Zeile 3 eingefügt (Zeile 5). Wenn Sie nun den so ermittelten, letzten Tag des Monats wieder in ein Datum verwandeln möchten, müssen Sie Tag, Monat und Jahr miteinander „verketten“ (Zeile 6) und mit Hilfe der DATWERT-Funktion in ein „richtiges“ Datum umwandeln (Zeile 7).
Wieso „richtiges“ Datum? Das Ergebnis der VERKETTEN-Funktion ist eine sog. Zeichenkette (String), mit der Excel nicht rechnen kann. Die DATWERT-Funktion verwandelt diese Zeichenkette wieder in eine Zahl (hier 42429). Diese Zahl steht für das Datum „29.02.2016“, das Sie dann korrekt sehen können, wenn Sie das Feld als Datum formatieren.
Einfache Lösung
Wenn Sie sich jetzt durch die Formel gekämpft haben, dann erfahren Sie jetzt, wie man sich das Leben mit dem Ultimo auch einfach machen kann: Liefern Sie der Funktion MONATSENDE ein beliebiges Datum und es errechnet den Ultimo (Zeile 9). Mit dem zweiten Argument (hier 0) können Sie sogar noch Monate dazu- oder wegnehmen.
Exkurs zum Schaltjahr
Das Jahr wird bestimmt durch die Zeit, die die Erde braucht, um einmal um die Sonne zu laufen, nämlich etwa 365,25 Tage. Durch diese etwa 6 Stunden, die die Erde länger als die genauen 365 Tage braucht, würde der Kalender alle vier Jahre um einen Tag der Sonne „hinterherhinken“. Nach ein paar Jahrzehnten oder Jahrhunderten, passt dann das Datum nicht mehr zur Jahreszeit. Aus diesem Grund fügt man alle vier Jahre einen Tag (nämlich den 29. Februar) ein.
Da die Abweichung aber auch nicht genau einen „Viertel-Tag“ beträgt, gibt es folgende Ausnahme: Die Jahre, die ein Jahrhundert abschließen (also auf „00“ enden), sind keine Schaltjahre.
Wenn Sie sich jetzt Ihren alten Kalender von 2000 rausholen und feststellen, dass das Jahr 2000 doch einen 29. Februar hatte, hier also die Ausnahme der Ausnahme: Alle Jahre, die sich durch 400 teilen lassen, sind doch wieder Schaltjahre.
Die oben entwickelte Formel liefert also lediglich bis 2099 korrekte Zahlen. Wenn Sie darüber hinaus weiterrechnen möchten, können Sie versuchen, die Formel aus Zeile 4 noch um die Sachverhalte „durch 100 teilbar“ oder „durch 400 teilbar“ zu erweitern. Viel Spaß dabei!