Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1176to1180
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1176to1180
1176to1180
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Monatsberechnung

Monatsberechnung
Mommonde
Hallo zusammen,
ich habe ein kleines Problem mit der Erstellung einer Excel Formel.
Ich bräuchte lediglich eine Übersicht wie viele Monate zwischen 2 Daten liegen und diese dann nach Jahr aufgeteilt.
Beispiel:
Anfangsdatum--------Enddatum
01.11.2009---------- 01.04.2010
01.04.2010 --------- 01.02.2011
Monate im Jahr 2009 = 1
Monate im Jahr 2009 = 5
Monate im Jahr 2009 = 0
Allerdings darf ( wenn es einen Dezember in der Datumsvorgabe gibt ) diese nicht im Anfangsjahr auftauchen sondern in dem folgenden Jahr ( erst dann erfolgt die Buchung )
Daher auch der "Logikfehler" in der Tabelle oben.
Super vielen großen Dank für die Hilfe !
Gruß
Christoph

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Monatsberechnung
29.09.2010 09:46:30
JogyB
Hallo Christoph,
geht es um komplette Monate? Oder auch um Teile von Monaten? D.h. was ist das Ergebnis bei 15.03.2010 bis 01.06.2010? Das kann je nach Rechenweise 2, 3 oder 4 sein.
Gruß, Jogy
AW: Monatsberechnung
29.09.2010 09:50:31
Mommonde
Hallo Jogy,
sobald der Monat "angekratzt" wurde soll Er brechnet werden, auch wenn es nur 1 Tag ist.
Gruß
Christoph
AW: Monatsberechnung
29.09.2010 10:49:54
JogyB
Hallo Christoph,
probier das mal aus:
=WENN(B$2>=A$2;WENN(JAHR(A$2)=D2;WENN((JAHR(B$2)>D2)+((JAHR(B$2)=D2)*(MONAT(B$2)=12)); 12-MONAT(A$2);MONAT(B$2)-MONAT(A$2)+1);0)+WENN((JAHR(B$2)=D2)*(JAHR(A$2)<>D2);MAX(MONAT(D2);11) +1;0)+WENN((JAHR(A$2)<D2)*(JAHR(B$2)>D2);12;0)+WENN((JAHR(B$2)=D2-1)*(MONAT(B$2)=12);1;0); "Fehler")
Startdatum in A2, Enddatum in B2, Jahr in D2. Die Formel kann man runterkopieren, das Jahr steht dann eben in D3, D4 usw.
Ich habe es ein wenig getestet, scheint zu gehen. Wenn Du einen Fehler bemerkst, dann sag mir bitte genau, bei welchen Datumswerten welches Ergebnis nicht passt.
Gruß, Jogy
Anzeige
AW: Monatsberechnung
29.09.2010 11:34:34
Mommonde
Hallo Jogy,
so ganz will die Formel nicht.
01.11.2009 ---------- 01.04.2010
2009 = 1 Monat ( ist ja wegen Dezember korrekt ) nur 2010 kommt die Formel auf 12 Monate nicht 5
01.04.2009 01.02.2011
Da kommt die Formel auf 8 Monate in 2009, aber es sind ja 7 da der Dezember noch raus muss...
Sorry wenn ich Dich quäle...
Gruß
Christoph
AW: Monatsberechnung
29.09.2010 12:31:01
JogyB
Hallo Christoph,
Da waren zwei Fehler drin, zum einen muss das MAX ein MIN sein, zum anderen stand da MONAT(D2) statt MONAT(B$2).
=WENN(B$2>=A$2;WENN(JAHR(A$2)=D3;WENN((JAHR(B$2)>D3)+((JAHR(B$2)=D3)*(MONAT(B$2)=12)); 12-MONAT(A$2);MONAT(B$2)-MONAT(A$2)+1);0)+WENN((JAHR(B$2)=D3)*(JAHR(A$2)<>D3);MIN(MONAT(B$2); 11)+1;0)+WENN((JAHR(A$2)<D3)*(JAHR(B$2)>D3);12;0)+WENN((JAHR(B$2)=D3-1)*(MONAT(B$2)=12);1;0); "Fehler")
Ich hoffe jetzt passt es... solche langen Formeln sind immer so übelst unübersichtlich ;)
Die 8 Monate stimmen aber: April, Mai, Juni, Juli, August, September, Oktober, November.
Gruß, Jogy
Anzeige
AW: Monatsberechnung
29.09.2010 13:23:58
BoskoBiati
Hallo,
wie wäre es damit:
Arbeitsblatt mit dem Namen 'Tabelle1'
 BCD
4001.12.200915.04.2011 
41200920102011
42 125

ZelleFormel
B42=WENN(ODER($B$40>=DATUM(B41;12;1);$C$40DATUM(B41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(B$41-1;12;1));MIN($C$40;DATUM(B$41;11;30));"M")+1)
C42=WENN(ODER($B$40>=DATUM(C41;12;1);$C$40DATUM(C41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(C$41-1;12;1));MIN($C$40;DATUM(C$41;11;30));"M")+1)
D42=WENN(ODER($B$40>=DATUM(D41;12;1);$C$40DATUM(D41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(D$41-1;12;1));MIN($C$40;DATUM(D$41;11;30));"M")+1)

Gruß
Bosko
Anzeige
AW: Monatsberechnung
29.09.2010 14:48:40
JogyB
Hallo Bosko,
dmit DATEDIF ist das natürlich schöner, nur bringt das nicht das Ergebnis, das er will. Es sollen ja alle auch nur angekratzten Kalendermonate gezählt werden.
Gruß, Jogy
AW: Monatsberechnung
29.09.2010 15:05:25
BoskoBiati
Hallo,
nur bringt das nicht das Ergebnis, das er will Wirklich?
Arbeitsblatt mit dem Namen 'Tabelle1'
 BCD
4030.11.200901.05.2011 
41200920102011
421126

ZelleFormel
B42=WENN(ODER($B$40>=DATUM(B41;12;1);$C$40DATUM(B41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(B$41-1;12;1));MIN($C$40;DATUM(B$41;11;30));"M")+1)
C42=WENN(ODER($B$40>=DATUM(C41;12;1);$C$40DATUM(C41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(C$41-1;12;1));MIN($C$40;DATUM(C$41;11;30));"M")+1)
D42=WENN(ODER($B$40>=DATUM(D41;12;1);$C$40DATUM(D41-1;11;30));"";DATEDIF(MAX($B$40;DATUM(D$41-1;12;1));MIN($C$40;DATUM(D$41;11;30));"M")+1)

Gruß
Bosko
Anzeige
AW: Monatsberechnung
29.09.2010 16:04:49
JogyB
Hallo Bosko,
gib mal 15.04.2009 und 05.05.2009 ein.
Gruß, Jogy
AW: Monatsberechnung
29.09.2010 17:07:36
BoskoBiati
Hallo Jogy,
überzeugt, Korrektur anbei:
Arbeitsblatt mit dem Namen 'Tabelle1'
 BCD
4015.04.200905.05.2009 
41200920102011
422  

ZelleFormel
B42=WENN(ODER($B$40>=DATUM(B41;12;1);$C$40DATUM(B41-1;11;30));"";DATEDIF(MAX(DATUM(JAHR($B$40);MONAT($B$40);1);DATUM(B$41-1;12;1));MIN(DATUM(JAHR($C$40);MONAT($C$40)+1;0);DATUM(B$41;11;30));"M")+1)
C42=WENN(ODER($B$40>=DATUM(C41;12;1);$C$40DATUM(C41-1;11;30));"";DATEDIF(MAX(DATUM(JAHR($B$40);MONAT($B$40);1);DATUM(C$41-1;12;1));MIN(DATUM(JAHR($C$40);MONAT($C$40)+1;0);DATUM(C$41;11;30));"M")+1)
D42=WENN(ODER($B$40>=DATUM(D41;12;1);$C$40DATUM(D41-1;11;30));"";DATEDIF(MAX(DATUM(JAHR($B$40);MONAT($B$40);1);DATUM(D$41-1;12;1));MIN(DATUM(JAHR($C$40);MONAT($C$40)+1;0);DATUM(D$41;11;30));"M")+1)

Gruß
Bosko
Anzeige
AW: Monatsberechnung
03.10.2010 01:07:15
FP
Hallo Edgar,
in B42:
=MAX(12*(B$41<JAHR($C$40+31))-MONAT($B$40)*(B$41=JAHR($B$40));(B$41=JAHR($C$40+31)) *(MIN(REST(MONAT($C$40)+{1.0};13))+1))
und: gib mal den Zeitraum 30.11.2009 bis 30.11.2013 ein und sieh Dir an, was dann bei Deiner Formel in G42 als Ergebnis ausgegeben wird...
Servus aus dem Salzkammergut
Franz
AW: Monatsberechnung
03.10.2010 07:57:46
BoskoBiati
Hallo Franz,
da hätte auch ein früherer 30.11. gereicht. War ein fehlendes Gleichheitszeichen, auch korrigiert:
Tabelle1

 ABCD
40 15.11.200930.11.2010
41 200920102011
42Falsch:112#ZAHL!
43richtig112 
44FP1120

Formeln der Tabelle
ZelleFormel
B42=WENN(ODER($B$40>=DATUM(B41;12;1); $C$40<DATUM(B41-1;11;30)); "";DATEDIF(MAX(DATUM(JAHR($B$40); MONAT($B$40); 1); DATUM(B$41-1;12;1)); MIN(DATUM(JAHR($C$40); MONAT($C$40)+1;0); DATUM(B$41;11;30)); "M")+1)
B43=WENN(ODER($B$40>=DATUM(B41;12;1); $C$40<=DATUM(B41-1;11;30)); "";DATEDIF(MAX(DATUM(JAHR($B$40); MONAT($B$40); 1); DATUM(B$41-1;12;1)); MIN(DATUM(JAHR($C$40); MONAT($C$40)+1;0); DATUM(B$41;11;30)); "M")+1)
B44=MAX(12*(B$41<JAHR($C$40+31))-MONAT($B$40)*(B$41=JAHR($B$40)); (B$41=JAHR($C$40+31)) *(MIN(REST(MONAT($C$40)+{1.0};13))+1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Deine Formel ist natürlich mal wieder in der Länge kaum zu toppen.
Gruß
Edgar
Anzeige
und noch einmal: Monatsberechnung
03.10.2010 10:55:31
Erich
Hi zusammen,
mit Franz' Formel bin ich noch nicht ganz einverstanden - siehe Ergebnis in C3:
 ABCDEFGHI
1  200820092010201120122013 
215.11.200930.11.20100112000Edgar
315.11.200930.11.201012112000Franz
415.11.200930.11.20100112000Erich
515.11.200930.11.20100112000Erich alt

Formeln der Tabelle
ZelleFormel
C2=WENN(ODER($A2>=DATUM(C$1;12;1); $B2<=DATUM(C$1-1;11;30)); 0; DATEDIF(MAX(DATUM(JAHR($A2); MONAT($A2); 1); DATUM(C$1-1;12;1)); MIN(DATUM(JAHR($B2); MONAT($B2)+1;0); DATUM(C$1;11;30)); "M")+1)
C3=MAX(12*(C$1<JAHR($B3+31))-MONAT($A3)*(C$1=JAHR($A3)); (C$1=JAHR($B3+31))*(MIN(REST(MONAT($B3)+{1.0};13))+1))
C4=WENN((JAHR($B4+31)<C$12)+(JAHR($A4+31)>C$12); 0; DATEDIF(MAX(12&1-C$12;$A4-TAG($A4)+2); MIN((12&-C$12)-1;$B4-TAG($B4)+4); "M")+1)
C5=WENN(($B5<1*(12&1-C$12))+($A5>=1*(12&-C$12)); 0;  DATEDIF(MAX(12&1-C$12;$A5-TAG($A5)+2); MIN((12&-C$12)-1;$B5-TAG($B5)+4); "M")+1)

In Zeile 5 steht die Formel, die ich am 1.10. gepostet hatte. Ist halt ein wenig länger als Franz' Formel.
Rückmeldung wäre nett! - Schöne Sonntagsgrüße von Erich aus Kamp-Lintfort
Anzeige
dann halt SUMME statt MAX
03.10.2010 17:23:39
FP
Hallo Erich,
stimmt, diesen Fall hatte ich nicht getestet.
Hier eine Korrektur - mit SUMME statt MAX:
=SUMME(12*(B$41<JAHR($C$40+31))-MONAT($B$40)*(B$41=JAHR($B$40));(B$41=JAHR($C$40+31)) *(MIN(REST(MONAT($C$40)+{1.0};13))+1)) 
Tabelle3
  
 ABC
39   
40 01.11.200930.11.2009
41 20092010
42   
43FP1 
44Erich1 
45Edgar1 
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Servus aus dem Salzkammergut

Franz

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: tut's aber auch nicht
                                            

                    

                                                    03.10.2010 18:35:10
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hi Franz,

SUMME statt MAX ändert nichts - mit 2008 in B41 kommt weiterhin 12 raus.

Kann auch nicht - vorher war MAX(12;0), jetzt ist es SUMME(12;0) = 12.

erwarten würde ich für 2008 eine 0:


 
 BCD
4001.11.200930.11.2009 
41200820092010
42   
431210

Formeln der Tabelle
ZelleFormel
B43=SUMME(12*(B$41<JAHR($C40+31))-MONAT($B40)*(B$41=JAHR($B40)); (B$41=JAHR($C40+31))*(MIN(REST(MONAT($C40)+{1.0};13))+1))
C43=SUMME(12*(C$41<JAHR($C40+31))-MONAT($B40)*(C$41=JAHR($B40)); (C$41=JAHR($C40+31))*(MIN(REST(MONAT($C40)+{1.0};13))+1))
 

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            Hallo du Quälgeist :D
                                            

                    

                                                    03.10.2010 20:24:01
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Erich,

dann halt so:

=MAX(;DATEDIF($B$40;MIN($C$40-TAG($C$40)+32;MAX($B$40;(1&-(B$41+1))));"m")-1-SUMME($A48:A48) +(B$41=JAHR($C$40+31))) 

und wehe Du findest wieder einen Fehler ;-)

Servus aus dem Salzkammergut

Franz

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            die Formel steht dabei in B48
                                            

                    

                                                    03.10.2010 20:27:21
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Erich,

hier meine Beispieltabelle


Tabelle3
  
 ABCDE
39     
40 01.11.200930.11.2010  
41 2008200920102011
42     
43FP12112 
44Erich 112 
45Edgar 112 
46FP #201120
47     
48FP #301120
49     
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            und noch eine
                                            

                    

                                                    03.10.2010 20:55:23
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Erich,

hab selbst noch einen Fehler gefunden - aber jetzt sollte es wirklich funktionieren:

in B49:

=(DATEDIF($B$40-TAG($B$40)+1;MIN($C$40-TAG($C$40)+32;MAX($B$40;(1&-C$41)-1));"m")-SUMME($A49:A49)) *(B$41<=JAHR($C$40+31)) 

Servus aus dem Salzkammergut

Franz

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Monatsberechnung
                                            

                    

                                                    29.09.2010 12:47:44
                                            

                                

                                            Mommonde
                                    

            

        

        

                            
Hallo Jogy,

passt perfekt !

Danke für die Hilfe !

Gruß

Christoph

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            kleine Verkürzung
                                            

                    

                                                    29.09.2010 15:02:58
                                            

                                

                                            JogyB
                                    

            

        

        

                            
Hallo Christoph,

das spart noch eine der WENN-Verknüfungen:

=WENN(B$2>=A$2;WENN(JAHR(A$2)=D2;MIN(12;MONAT(B$2)+(JAHR(B$2)-JAHR(A$2))*12+1)-MONAT(A$2);0) +WENN((JAHR(B$2)=D2)*(JAHR(A$2)<>D2);MIN(MONAT(B$2);11)+1;0)+WENN((JAHR(A$2)<D2)*(JAHR(B$2) >D2);12;0)+WENN((JAHR(B$2)=D2-1)*(MONAT(B$2)=12);1;0);"Fehler") 

Gruß, Jogy

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Monatsberechnung
                                            

                    

                                                    01.10.2010 09:13:59
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hi Christoph,

hier noch eine Alternative:


 
 ABCDEFGH
1vonbis200820092010201120122013
215.04.200905.05.2009020000
301.01.200931.12.20090111000
405.01.200903.12.20090111000
501.12.201120.12.2011000010
601.10.201011.10.2010001000
701.12.201011.12.2010000100
831.12.201001.12.20110001210

Formeln der Tabelle
ZelleFormel
C2=WENN(($B2<1*(12&1-C$1))+($A2>=1*(12&-C$1)); 0; DATEDIF(MAX(12&1-C$1;$A2-TAG($A2)+2); MIN((12&-C$1)-1;$B2-TAG($B2)+4); "M")+1)
 

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


            


    
    


            
    
    
    
                    


    
    
    
    
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    


    
Anzeige

    

        
                    
                
    




    
    

    

        

            

                
                    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
                
            

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                                    

                        


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

                    

                                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige