Rechnungsabgrenzungsposten automatisch ermitteln

[lduser]

Hallo liebes Forum,

ich arbeite zur Zeit an einer Tabelle um Rechnungsabgrenzungsposten automatisch berechnen zu können.
Zum allgemeinen Verständnis: Jeden Monat kommen Rechnungen rein, welche als Wartungsverträge, Nutzungsgebühren etc bereits im Voraus über einen bestimmten Zeitraum (mal nur ein paar Monate, manchmal über mehrere Jahre hinweg) bezahlt werden. Der Rechnungsbetrag soll nun Tag genau (30 Tage pro Monat/360 Tage pro Jahr) aufgeteilt werden.
Das heißt, wenn der Abrechnungszeitraum vom 01.01.2021 - 31.01.2021 dann soll jeder Monat einen gleich hohen Betrag erhalten.
Es kann aber auch vorkommen, dass der Abrechnungszeitraum nicht am Anfang des Monat startet, sondern bspw. erst am 18.01.2021 oder 31.01.2021 (bzw. auch in anderen Monaten/Jahren).
Da ich selber ein ziemlicher Excel Anfänger bin, wollte ich nun um eure Hilfe Fragen.
Ich habe es bereits hinbekommen, dass wenn der Abrechnungsmonat am 01. beginnt und am 31. endet, dass die WENN Formel den Betrag für die bestimmten Monate berechnet und auch nicht über die Monate hinaus den Betrag berechnet (Siehe Anhang Variante 2).

Formel: =WENN(UND(L$6>=$H9;L$6<=$I9);$G9/(TAGE360($H9;$I9))*(TAGE360(L$6;M$6));"")

Diese Formel kann aber die Tage nicht genau berechnen, das heißt, wenn der Monat nicht am 01. beginnt, dann gibt die Formel logischerweise keinen Wert aus.
Deswegen habe ich Variante 1 entwickelt und nun kann ich die Tage genau berechnen und das funktioniert auch alles super, bis zu dem Zeitpunkt, an welchem der Abrechnungszeitraum am 30./31. beginnt.

Formel: =WENN(UND(L$6>=$H7;L$6<=$I7);$G7/(TAGE360($H7;$I7))*(TAGE360(L$6;M$6));($G7/(TAGE360($H7;$I7)))*(TAGE360($H7;M$6)))

Dann kann die Formel den Wert nicht mehr korrekt berechnen und berechnet einen Tag mehr, als benötigt wird.
Zusätzlich dazu berechnet die Formel logischerweise auch mehr Monate als nötig wären, was jedoch auch fehlerhaft ist.
Ebenfalls fehlt auch noch, dass die Formel genau andersherum auch funktioniert, dass heißt, dass die Formel für den letzten Monat auch nur so viele Tage berechnet, wie lang der bestimmte Abrechnungszeitraum ist. Das heißt, wenn der Abrechnungszeitraum am 25. endet, dann sollen im letzten Monat auch nur 25 Tage berechnet werden und nicht 30.
Ich weiß, dass das Ganze wahrscheinlich etwas komplizierter wird, aber dennoch würde ich mich sehr über eure Hilfe freuen!
MfG

[Mase]

pro rata temporis gilt doch auch bei ARAP/PRAP oder verstehe Ich Dich falsch?

[Der Steuerfuzzi]

Hallo,

probier es mal so:

Code:

L7 =WENN(ODER($H7>=L$6;$I7<=MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0));WAHR)+1;0);30)*$G7/(TAGE360($H7;$I7;WAHR)+1)

[lduser]

Hallo,

probier es mal so:

Code:

L7 =WENN(ODER($H7>=L$6;$I7<=MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0));WAHR)+1;0);30)*$G7/(TAGE360($H7;$I7;WAHR)+1)

Danke, das funktioniert super für alle Monate bis zu dem Moment, wenn im Februar der Abrechnungszeitraum startet, denke das hat was damit zu tun, dass der Monat weniger Tage hat, als die anderen Monate.
Gibt es dafür ne Lösung, weil ansonsten würde die Formel ja perfekt passen.

---

pro rata temporis gilt doch auch bei ARAP/PRAP oder verstehe Ich Dich falsch?

Ich glaube wir verstehen uns da falsch ^^

[Der Steuerfuzzi]

Dann betrachte einen Monat als voll wenn das Startdatum der Monatserste und das Enddatum der Monatsletzte ist. Das ganze sieht dann so aus:

Code:

=WENN(ODER($H7>L$6;$I7<MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0));WAHR)+1;0);30)*$G7/(TAGE360($H7;$I7;WAHR)+1)

Oder ist es nicht das, was Du meinst?

Ansonsten mach mal ein Beispiel.

[lduser]

Dann betrachte einen Monat als voll wenn das Startdatum der Monatserste und das Enddatum der Monatsletzte ist. Das ganze sieht dann so aus:

Code:

=WENN(ODER($H7>L$6;$I7<MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0));WAHR)+1;0);30)*$G7/(TAGE360($H7;$I7;WAHR)+1)

Oder ist es nicht das, was Du meinst?

Ansonsten mach mal ein Beispiel.

Die Formel funktioniert super und auch wirklich perfekt, wenn "Abgrenzung von" in jedem Monat startet, außer in dem Monat Februar. Wenn "Abgrenzung von" nun z.B. der "10.02.2021" oder der "28.02.2021" etc. (außer der 01.02.2021) ist, dann kommt als Summe nicht 1200€ raus, sondern 1192,638037€ bzw immer weniger, je später es im Februar beginnt.

[Der Steuerfuzzi]

Habe es mal umgebaut. Bitte teste diese Formel mal eingehend:

Code:

=WENN(ODER($H7>L$6;$I7<MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0)+1);WAHR);0);30)*$G7/(TAGE360($H7;$I7+1;WAHR))

[lduser]

Klappt perfekt, vielen Dank für die Mühe!

Edit.: Klappt doch nicht perfekt...

---

Habe es mal umgebaut. Bitte teste diese Formel mal eingehend:

Code:

=WENN(ODER($H7>L$6;$I7<MONATSENDE(L$6;0));MAX(TAGE360(MAX(L$6;$H7);MIN($I7;MONATSENDE(L$6;0)+1);WAHR);0);30)*$G7/(TAGE360($H7;$I7+1;WAHR))

Nun klappt es zwar, im Februar mit allen Daten die "Abgrenzung von" zu starten, jedoch kann ich jetzt nicht mehr das "Abgrenzung bis" Datum ändern. Das heißt, dass wenn ich "Abgrenzung bis" auf den 30.11.2021 setzte funktioniert alles, jedoch wenn ich es auf einen anderen Tag außer Monatsende setzte wie z.B. den 15.11.2021 (oder auch den 01.11.2021), dann kommt als Wert nur noch ein geringerer Betrag als die 1200€ raus.

[lduser]

Klappt perfekt, vielen Dank für die Mühe!

Edit.: Klappt doch nicht perfekt...

---

Nun klappt es zwar, im Februar mit allen Daten die "Abgrenzung von" zu starten, jedoch kann ich jetzt nicht mehr das "Abgrenzung bis" Datum ändern. Das heißt, dass wenn ich "Abgrenzung bis" auf den 30.11.2021 setzte funktioniert alles, jedoch wenn ich es auf einen anderen Tag außer Monatsende setzte wie z.B. den 15.11.2021 (oder auch den 01.11.2021), dann kommt als Wert nur noch ein geringerer Betrag als die 1200€ raus.

Ich habe nun die Formel abgewandelt:

=WENN(ODER($H7>L$6;$I7
Nun wird überall die richtige Summe gebildet. Das Problem ist jetzt jedoch, dass immer wenn bei "Abgrenzung von" der 01. steht, dass die Formel dann den Wert für den vorherigen Monat für einen Tag berechnet bzw auch anders herum.

Das heißt, dass z.B. "Abgrenzung von" 02.02.2021 bis "Abgrenzung bis" 01.02.2022 dazu führt, dass zwar der Monat Februar in 2022 einen Tag berechnet bekommt (was auch richtig ist), aber der Februar 2021 gleich berechnet wird wie alle anderen Monate, obwohl er einen Tag weniger hat.

Gibt es dafür eine Lösung?

[Der Steuerfuzzi]

Habe im Moment leider nicht so viel Zeit. Schaue mir das mal an, wenn ich etwas Luft habe.