Automatische Berechnung von Stichtagen in Excel über Eingabe eines Ausgangsdatums?
Hallo liebe Gute-Frage-Experten,
ich habe mein Problem mit nachfogendem Screenshot dargestellt.
Ich möchte in ein Eingabefeld ein Datum eingeben (immer Monatsultimo).
Hieraus sollen sich vier Felder automatisch berechnen:
Aktueller Stichtag (z.B. 08.2016), letzter Quartalsstichtag (z.B. 06.2016), Vor-Quartalsstichtag (z.B. 03.2016) und der Vor-Vorquartalsstichtag (z.B. 12.2015). 03,06,09,12 sind dabei immer die Vorquartale. Nur der aktuelle Stichtag kann auch ein Monat dazwischen sein.
Mit dem erstgenannten und den beiden letztgenannten habe ich keine Probleme. Mir gelingt es nicht, den letzten Quartalsstichtag richtig berechnen zu lassen, da es ja verschiedene Ausgangsstichtage gibt (Juli bedeutet: -1; August bedeutet: -2 und erst im September gilt wieder -3). Ich habe versucht, dies über die angegeben Formel zu realisieren. Leider werden meine beiden SONST-Bedingungen immer ignoriert. Er rechnet immer nur -1. Folglich stimmen dann auch die Vorquartale nicht.
Bin ich zu kompliziert unterwegs? Ich hoffe auf Hilfe, habe leider in google mein konkretes Problem nicht finden können.
Ich danke schon jetzt herzlich!
Gaby
3 Antworten
- Das (auf D1 bezogene) aktuelle Quartal ist(nur Zahl, kein Datum): =GANZZAHL(MONAT(D1)/3)+1
- Das Ende des Vor-Quartals ist:
- =EDATUM(MONATSENDE(D1;0);-REST(MONAT(D1);3))
- Das Ende des aktuellen Quartals ist:
- =EDATUM(MONATSENDE(D1;0);-REST(MONAT(D1);3)+3)
- Das Ende des nächsten Quartals ist:
- =EDATUM(MONATSENDE(D1;0);-REST(MONAT(D1);3)+6)
- Du kannst nun beliebig voraus- oder zurückzählen, das funktioniert auch über die Jahresgrenzen hinweg. Also speziell der gefragte Letzte des Vor-Vor-Quartals:
- =EDATUM(MONATSENDE(D1;0);-REST(MONAT(D1);3)-3)
Wenn Du das als MM.JJ formatierst, bekommst du nur Monat.Jahr angezeigt, die Rechnung geht aber immer auf genau den Letzten des Quartals.
Kein Bild. Kann das so abstrakt im Moment nicht nachvollziehen, werd aber mal rumprobieren.. Du musst eine eigene AW erstellen und dort einen Screenshot einfügen. Ob 2 gehen, weiß ich nicht.
Hallo lamian,
ja, das habe ich auch gemerkt mit den Screenshots...
Ich habe das nun wiederum versucht,umzusetzen.
In der ersten Lösung habe ich nicht verstanden, was mit der Formel Ganzzahl passiert. Diese wird doch in deiner Lösung nicht gebraucht, oder?
Ich habe nun jedenfalls deine zweite Lösung probiert und noch etwas geändert. Bin nicht sicher, ob wir ganz das gleiche gemacht haben...
Bei mir ist jetzt:
E4: =D1(aktueller Stichtag/Monatsultimo - 07.2016)
D4: =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)-1);0) (--> Ultimo des letzten Quartalsstichtages - 06.2016)
C4: =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)-3-1);0) (--> Ultimo des vorletzten Quartalsstichtages - 03.2016)
B4: =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)-6-1);0) (Ultimo des vor-vor-letzten Quartalsstichtages - 12.2015)
Und das funktioniert - egal ob ich aktuell einen Quartalsultimo oder einen Ultimo der Monate dazwischen vorgebe.
Das hätte ich niemals alleine hinbekommen! Herzlichen Dank!!
Liebe Grüße
Gaby
DH! solche "Kunden" wünschte ich mir immer! Die Datumsformeln in xl gehören immerhin zur Kategorie der kompliziertesten!
Ebenso danke für die Rückmeldung, auch das weiß ich zu schätzen! Du bist ja sehr neu hier, deshalb der Tipp: ich hab mich schon vor Jahren für nicht schlecht in xl gehalten, aber ich habe hier von einigen Meistern (nicht alle davon melden sich oft) sehr viel dazugelernt, was ich besser schon zu Berufszeiten gekonnt hätte. Bleib also hier präsent und knacke gelegentlich die Nüsse zur eigenen Vervollkommnung, soweit es Deine Zeit zulässt!
- Vorquartal:
- =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)-1);0)
- Quartal des Stichtags:
- =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)+3-1);0)
- Quartal danach:
- =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)+6-1);0)
- VorVorquartal: =MONATSENDE(EDATUM(MONATSENDE(D1;0);-REST((MONAT(D1)-1);3)-3-1);0)
Ich hab das ganz pragmatisch experimentell entwickelt, kann durchaus sein, dass es einfacher ginge. Der erste Fehler war, dass bereits Ganzzahl(9/3) 3 ergibt, musste ich kompensieren und an anderer Stelle gegenkompensieren. Danach wurde der Datumstag der Eingabe übernommen, deshalb ein zweites Mal Monatsende.
Wenn Du nochmal einen Fehler entdeckst, melde Dich wieder, ich hab das mit einer Reihe von Terminen jetzt positiv getestet.
Ich haben nun eben den Weg über eine weitere Antwort gewählt und bin nicht sicher, ob Du diese nun überhaupt erhälst. Daher hier noch einmal der Hinweis auf meine Antwort an Dich. Habe beschrieben, wie ich es nun abschließend gemacht habe. Vielen Dank nochmals!
ich hab sie erhalten und bin meinem ersten Eindruck nach zu genau demselben Ergebnis gekommen, DH und siehe meinen Kommentar dort.
Ich wollte nur noch festhalten, dass von meinen beiden AW diese hier die richtige ist.
Hallo lamiam,
vielen Dank für die Antwort.
Ich habe mal versucht, die Formeln so einzusetzen, wie es mir sinnvoll erschien...
Grundsätzlich funktioniert das so. Wenn es sich jedoch bei dem aktuellen Stichtag um einen Quartalsstichtag (03,06,09,12) handelt, wird das Vorquartal nicht richtig berechnet sondern dem aktuellen Quartalsstichtag gleichgesetzt. Wenn ich als aktuellen Stichtag die Monate zwischen den Quartalen habe, ist die Rechnung richtig.
Siehe Screenshot - ich hänge da noch zwei weitere Bilder in meinen ersten Beitrag. Hoffe, das geht und man darf das auch ...
Vielleicht habe ich die Formeln aber auch nicht richtig eingsetzt...
Liebe Grüße