Automatische Berechnung von Stichtagen in Excel über Eingabe eines Ausgangsdatums?

... komplette Frage anzeigen

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.

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Kurvenstar
06.08.2016, 14:38

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

0
  • 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.

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Kurvenstar
06.08.2016, 16:36

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!

0

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

Antwort bewerten Vielen Dank für Deine Bewertung
Kommentar von Iamiam
06.08.2016, 18:50

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!

2

Was möchtest Du wissen?