Excel Beträge spitz rechnen

[Doc01]

Hallo,

ich würde gerne in Excel Gebühren für einen bestimmten Zeitraum ausrechnen.
Diese müssten tageweise „spitz“ ausgerechnet werden, also volle Monate mit dem Monatsbetrag; unabhängig wieviele Tage der Monat hat.
Beispiel: Gebühr: 100,00 

Zeitraum: 15.08.2018 bis zum 18.10.2018

Gebühr für August (15/31 Tage) = 48,39
September (voller Monat) = 100,00 (Ohne weitere Berechnung, weil der ganze Monat zählt)
Oktober (18/31 Tage) = 58,06

Gesamt: 206,45
Eingegeben werden soll am Besten nur  Höhe der Gebühr und der Zeitraum (Beginn, Ende)

Wäre super, wenn mir da einer helfen könnte….

Danke schon mal für die Mühe!

[RPP63]

Moin!
Für so etwas gebraucht der Finanzmensch eigentlich TAGE360()

	A	B	C
1	100,00 €
2	15.08.2018	18.10.2018	210,00 €

Zelle	Formel
C2	=TAGE360(A2;B2;WAHR)/30*A1

Gruß Ralf

[BoskoBiati]

Hi,

wenn es so gerechnet werden soll wie verlangt:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	15.08.2018	18.10.2018	206,45

Zelle	Formel
C1	=MIN(100;(TAG(MONATSENDE(A1;0))-1-(TAG(A1)))/0,31)+MAX(0;(JAHR(B1)-JAHR(A1))*12-MONAT(A1)+MONAT(B1)-1)*100+TAG(B1)/0,31

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[LCohen]

Die korrekte Einzel-Formel dafür ist ziemlich nervig, da sie ja im Zweifel auch über den Jahreswechsel gehen können muss und die Monate unterschiedliche Tage haben. Bosko bspw. hat 31, obwohl es auch 28-30 gibt. Außerdem kommt er seltsamerweise trotz des Fehlers im August (17 statt 15 Tage) auf centgenau den Betrag, was nicht sein dürfte. 

Bosko: Was ist vom 2.10-26.10? Was ist vom 27.9-4.10?

Korrekt ist eine 3-Fall-Unterscheidung: Start und Ende ...

• im selben Monat
  
• in direkt benachbarten Monaten
  
• mit ganzen Monaten dazwischen
  

Und beide (Start und Ende) haben ihre eigene Länge!

Daher lege ich Dir folgendes nahe:

D1: 1.1.18
E1:O1: =D1+32-TAG(D1+31)

A2: Starttag (17 Tage im August, nicht 15! Oder 16, falls der 1. Tag nicht zählen soll)
B2: Endtag
C2: 100
D2:O2: =RUNDEN(MAX(;MIN(E$1;$B2+1)-MAX(D$1;$A2))*$C2/(E$1-D$1);2)

Nun erhältst Du nebenbei auch die Monatssummen über alle Verleihungen.

[BoskoBiati]

Hi,

lupo, Du hast in einem Punkt recht, ich habe mich da verleiten lassen. Richtig müsste es so aussehen, Jahreswechsel sind berücksichtigt:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Start	Ende	Monatsgeb.	Summe
2	15.08.2018	18.10.2018	100,00 €	209,68 €
3	12.10.2018	15.06.2019	100,00 €	809,68 €

Zelle	Formel
D2	=MIN(C2;(TAG(MONATSENDE(A2;0))-(TAG(A2)))/TAG(MONATSENDE(A2;0))*C2)+MAX(0;(JAHR(B2)-JAHR(A2))*12-MONAT(A2)+MONAT(B2)-1)*C2+TAG(B2)/TAG(MONATSENDE(A2;0))*C2
D3	=MIN(C3;(TAG(MONATSENDE(A3;0))-(TAG(A3)))/TAG(MONATSENDE(A3;0))*C3)+MAX(0;(JAHR(B3)-JAHR(A3))*12-MONAT(A3)+MONAT(B3)-1)*C3+TAG(B3)/TAG(MONATSENDE(A3;0))*C3

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Doc01]

Erstmal vielen Dank für eure Mühe und schnellen Antworten!

Ich probiere das später mal aus und würde nochmal Feedback geben!

[LCohen]

Beginn und Ende in einem Monat noch falsch, Bosko.

Ansonsten bist Du schon kürzer, als ich dachte, es schaffen zu können. 

Vielleicht kriegst Du das letzte ja auch noch hin. Dann wäre das einen excelformeln.de-Eintrag wert.

[BoskoBiati]

Hi,


nochmal korrigiert:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Start	Ende	Monatsgeb.	Summe
2	15.08.2018	18.10.2018	100,00 €	209,68 €
3	12.10.2018	15.06.2019	100,00 €	811,29 €
4	15.08.2018	27.08.2018	100,00 €	38,71 €

Zelle	Formel
D2	=DATEDIF(A2-TAG(A2);MONATSENDE(B2;0);"M")*C2-((TAG(MONATSENDE(B2;0))-TAG(B2))/TAG(MONATSENDE(B2;0))+TAG(A2)/TAG(MONATSENDE(A2;0)))*C2
D3	=DATEDIF(A3-TAG(A3);MONATSENDE(B3;0);"M")*C3-((TAG(MONATSENDE(B3;0))-TAG(B3))/TAG(MONATSENDE(B3;0))+TAG(A3)/TAG(MONATSENDE(A3;0)))*C3
D4	=DATEDIF(A4-TAG(A4);MONATSENDE(B4;0);"M")*C4-((TAG(MONATSENDE(B4;0))-TAG(B4))/TAG(MONATSENDE(B4;0))+TAG(A4)/TAG(MONATSENDE(A4;0)))*C4

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[LCohen]

Das sieht schon gut aus. Allerdings gibt es noch folgendes:

PHP-Code:

27. Sep    01. Dez    100    213,3333333
28. Sep    02. Dez    100    213,3333333
29. Sep    03. Dez    100    213,3333333
27. Okt    01. Jan    100    216,1290323
28. Okt    02. Jan    100    216,1290323
29. Okt    03. Jan    100    216,1290323 


Da die Randmonate der ersten 3 Zeilen unterschiedlich viele Tage haben, müsste der Betrag dort leicht variieren, und zwar um 100/30-100/31=0,10752688 pro Tag Verschiebung geringer werdend. So ist jedenfalls die Aufgabenstellung. Das macht sie gerade so schwierig - bzw. die Formel so lang - und den Hinweis von RPP63 so berechtigt.

Richtig finde ich bei Dir, dass Du die Differenz ziehst (also anders als bei NETTOARBEITSTAGE, wo inklusiv summiert wird). Allerdings muss TE dann einen Gegenstand vom 3.10-16.10 und dann vom 16.10-21.10 vergebühren, statt erst ab 17.10. Hoffentlich tut er das dann auch.

[BoskoBiati]

Hi,

das passt:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
5	27.09.2018	01.12.2018	100,00 €	213,23 €
6	28.09.2018	02.12.2018	100,00 €	213,12 €
7	29.09.2018	03.12.2018	100,00 €	213,01 €
8	27.10.2018	01.01.2019	100,00 €	216,13 €
9	28.10.2018	02.01.2019	100,00 €	216,13 €
10	29.10.2018	03.01.2019	100,00 €	216,13 €

Zelle	Formel
D5	=DATEDIF(A5-TAG(A5);MONATSENDE(B5;0);"M")*C5-((TAG(MONATSENDE(B5;0))-TAG(B5))/TAG(MONATSENDE(B5;0))+TAG(A5)/TAG(MONATSENDE(A5;0)))*C5

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg