Mit Formel Obergrenze den letzten Tag eines Quartals bestimmen. Kürzeste Lösung, entwickelt vom Sternenhimmelstuermer.eu 

Als Nebenprodukt einer Abhandlung entwickelte der Sternenhimmelstuermer eine Alternative, den letzten Tag eines Quartals für ein beliebiges Datum mit der Formel Obergrenze zu berechnen. Der Sternenhimmelstuermer fand am 20.04.2011 in keiner Suchmaschine eine adäquate Lösung, die ähnlich leicht und kurz war. Die bisher genialste Lösung mit einer Rundungsformel finden Sie hier - die ist aber schwerer nachvollziehbar und länger.

Der Sternenhimmelstuermer geht dabei den Weg über die Formel Obergrenze:

fx	=DATUM(JAHR(A1);OBERGRENZE(MONAT(A1);3)+1;1)-1
	A	B	C	D
1	03.04.2011	30.06.2011	Zelle C1	Zelle D1
2	Zelle A 2	Zelle B2	Zelle C 2	Zelle D2
3
Erklärung: In der Zelle A1 steht ein beliebiges Datum in der Form tt.mm.jjjj. In der Zelle B1 steht das gerundete Ergebnis. In der Funktionszeile die Formel, die hinter der Berechnung steht. Grundgedanke: Sie Runden den Monat auf das nächste Quartal auf, addieren einen Monat hinzu und setzen den Tag des Urprungsdatums auf den ersten Tag. Danach ziehen Sie einen Tag ab und erhalten so den letzten Tag des dritten Monats eines Quartals. Der Sternenhimmelstuermer ist mathematisch gesehen eine Niete und beneidet Menschen, die mit urigen Formeln den letzten Tag eines Quartals errechnen. Aber er weiß auch, dass der Abzug eines Tages von einem Monat nach dem letzten Tag immer in Excel das richtige Ergebnis liefert... Der Weg: Mit dem ersten Teil der Formel zerlegen Sie das Datum in Jahr, Monat und Tag. Die Grundformel heißt Datum. Das erste Argument bis zum Semikolon ist das Jahr von A1 (2011 - Jahr ist die Funktion für die Anzeige des Jahres von A1 aus dem Datum). Danach erwartet die Funktion Datum als zweites Argument den Monat nach dem ersten Semikolon. Hier verschachteln Sie die Funktion Obergrenze. Wie gewohnt wird diese mit einer Klammer geöffnet. Nun wird die Funktion Monat von A1 (04) gewählt. Die 3 nach dem Semikolon ist der Aufruf für Excel in dreierschritten aufzurunden, also im o. a. Beispiel der sechste Monat. Mit dem Schließen der Klammer ist die Funktion Obergrenze nun beendet. Wir befinden uns auf der nächst höheren Ebene aber in der Funktion Datum beim Monat. Deshalb ist es möglich mit +1 einen Monat zum Ergebnis hinzuzuaddieren, so dass wir das Ergebnis der Rundung 6 in diesem Beispielfall mit 1 addieren. Nach dem Semikolon wählen Sie den ersten Tag. Danach ist die Formel Datum beendet und eine Klammer wird gesetzt. Das Datum heißt also an dieser Stelle 01.07.2011. Von diesem Datum ziehen wir einen Tag - 1 ab. Sie müssen dazu wissen, Das Excel grundsätzlich bei einer Subtraktion von Zahlen von einem Datum Tage verwendet. Das Ergebnis ist also der letzte Tag des Quartals.. Das geniale an dieser Methode ist, dass Sie nicht wissen müssen, ob der Monat 30 0der 31 Tage hat. Excel nimmt Ihnen die Entscheidung ab. Komisch, dass scheinbar keiner diesen Dreh für dieses Problem anwendet und bisher nur Lösungen mit Indexen und ähnlichen komplizierten Formeln verwendet werden... Wer das Prinzip verstanden hat, kann die Obergrenzenformel innerhalb der Datumsfunktion beliebig bei Tagen, Monaten oder Jahren verschachteln. Denken Sie  daran, dass die Schrittstufe der Obergrenzenformel je nach Verwendung angepasst werden kann. Liegt ein gewünschtes Ergebnis nicht in der Schrittfolge, dann passen sie das mit Addition oder Subtraktion an. Der Sternenhimmelstuermer wünscht Ihnen viel Erfolg!

 

Impressum
Datenschutz