Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1348to1352
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Taggenaue Abrechnung

Taggenaue Abrechnung
03.03.2014 08:03:11
Rene
Hallo,
ich stehe vor folgender Aufgabe. Ich habe eine Tabelle mit 6 Einträgen (Kategorien) für monatliche Beträge. Die Abrechnung erfolgt aber taggenau, das heißt, obwohl es für jeden Monat gleich viel Geld pro Kategorie gibt, sind die Tagessätze ja unterschiedlich (wegen der unterschiedlichen Anzahl an Tagen). Wie kann ich den konkreten Betrag für einen Zeitraum ausrechnen. Ich habe mir eine Tabelle gebaut, wobei meine Schwierigkeit ist, wie ich die Tage im jeweiligen Monat automatisch ermitteln kann (orange Hinterlegung).
Vielleicht gibt es aber auch noch eine elegantere Lösung, bitte unbedingt aber nur mit Formeln - ohne Makro.
Hier mal mein Beispiel: https://www.herber.de/bbs/user/89513.xls

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Taggenaue Abrechnung
03.03.2014 09:12:16
UweD
Hallo
Vorab:
Ich hab deine Starren Werte mal durch Formeln getauscht..
Kopiere die Formeln A8:E8 runter bis A19:E19
Tabelle1
 ABCDEFGH
1               
2vonbisKategorieSumme       
317.01.201410.04.20143 86,45 €        
4               
5               
6               
7  max Tage pro Monat  Tagessatz für akt. MonatSumme für Abrechnungszeitraum  KategorieBeträge
8Januar3117 0,97 € 16,45 €   120
9Februar2828                           1,07 €                                      30,00 €   225
10März3131                           0,97 €                                      30,00 €   330
11April3010                           1,00 €                                      10,00 €   440
12Mai31        550
13Juni30        660
14Juli31           
15August31           
16September30           
17Oktober31           
18November30           
19Dezember31           

verwendete Formeln
Zelle Formel Bereich
D3 =SUMME(E8:E19) 
A8 =DATUM(JAHR($A$3);ZEILE(A1);1) 
B8 =DATUM(JAHR(A8);MONAT(A8)+1;0) 
C8 =WENN(MONAT(A8)>MONAT($B$3);"";WENN(A8<$A$3;TAG($A$3);WENN(B8>$B$3;TAG($B$3);TAG(B8)))) 
D8 =WENN(C8<>"";SVERWEIS($C$3;$G$7:$H$13;2;FALSCH)/TAG(B8);"") 
E8 =WENN(C8<>"";C8*D8;"") 

Tabellendarstellung in Foren Version 5.49


Gruß UweD

Anzeige
AW: Taggenaue Abrechnung
03.03.2014 10:40:39
Rene
ja super, das hat geholfen. Vielen Dank.

Fehler in Formel
03.03.2014 11:01:14
Erich
Hi zusammen,
Uwes Formel in C8 rechnet mit "TAG($A$3)" nicht richtig. Hier sollte nicht 17,
sondern die Anzahl der Tage vom 17. bis Ende Januar stehen.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Fehler in Formel
03.03.2014 13:06:31
UweD
Hallo nochmal.
Stimmt.
Meine Formel müsste so aussehen:
=WENN(MONAT(A8)>MONAT($B$3);"";WENN(A8<$A$3;B8-$A$3;WENN(B8>$B$3;TAG($B$3);TAG(B8))))
aber die von Erich ist viel eleganter.
Gruß UweD

AW: uupps
03.03.2014 13:10:16
UweD
+1
=WENN(MONAT(A8)>MONAT($B$3);"";WENN(A8<$A$3;B8-$A$3+1;WENN(B8>$B$3;TAG($B$3);TAG(B8))))

Anzeige
Taggenaue Abrechnung
03.03.2014 10:17:19
Erich
Hi Rene,
hier noch zwei Vorschläge:
Tabelle1

 ABCDEFGH
2vonbisKategorieSummeKat-Betrag   
317.01.201410.04.2014384,5230   
4        
5        
6        
7Monmax T/MonTageTagessatz akt. MonatSumme Abr.-zeitr. KategorieBeträge
8Januar31150,9714,52 120
9Februar28281,0730,00 225
10März31310,9730,00 330
11April30101,0010,00 440
12Mai3100,970,00 550
13Juni3001,000,00 660
14Juli3100,970,00   
15August3100,970,00   
16September3001,000,00   
17Oktober3100,970,00   
18November3001,000,00   
19Dezember3100,970,00   

Formeln der Tabelle
ZelleFormel
D3=SUMME(E8:E19)
E3=SVERWEIS($C$3;$G$7:$H$13;2;0)
A8=DATUM(JAHR($A$3); ZEILE(A1); 1)
B8=DATUM(JAHR(A8); MONAT(A8)+1;0)
C8=MAX(;MIN(B$3;B8)-MAX(A$3;A8)+1)
D8=E$3/TAG(B8)
E8=C8*D8


Tabelle2

 ABCDEFGH
2vonbisKategorieSummeKat-Betrag   
317.01.201410.04.2014384,5230   
4        
5        
6        
7Monmax T/Mon Summe Abr.-zeitr.  KategorieBeträge
8Januar31 14,52  120
9Februar28 30,00  225
10März31 30,00  330
11April30 10,00  440
12Mai31 0,00  550
13Juni30 0,00  660
14Juli31 0,00    
15August31 0,00    
16September30 0,00    
17Oktober31 0,00    
18November30 0,00    
19Dezember31 0,00    

Formeln der Tabelle
ZelleFormel
D3=SUMME(D8:D19)
E3=SVERWEIS($C$3;$G$7:$H$13;2;0)
A8=DATUM(JAHR($A$3); ZEILE(A1); 1)
B8=DATUM(JAHR(A8); MONAT(A8)+1;0)
D8=MAX(;MIN(B$3;B8)-MAX(A$3;A8)+1)*E$3/TAG(B8)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige