Kim hat ein Arbeitsblatt, in das sie ein Startdatum (Spalte C) und ein Enddatum (Spalte D) einfügt. Sie braucht eine Möglichkeit, die Anzahl der Monatsenden zwischen den beiden Daten zu berechnen. Wenn beispielsweise das Startdatum der 1.1.17 und das Enddatum der 31.07.17 ist, beträgt die Anzahl der Monatsende 7. Wenn das Enddatum jedoch der 30.07.17 ist, die Anzahl der Monate endet ist 6.

Es gibt eine beliebige Anzahl von Formeln, die Sie entwickeln können, um die richtige Anzahl zu berechnen. Die einfachste Formel ist, wenn Sie immer darauf zählen können, dass das Datum in Spalte C der Beginn eines Monats ist. In diesem Fall funktioniert die folgende Formel einwandfrei:

=DATEDIF(C1,D1+1,"m")

Wenn Sie nicht auf Spalte C zählen können, die ein Datum enthält, das der Beginn eines Monats ist, aber Sie können darauf zählen, dass das Anfangs- und Enddatum immer im selben Jahr liegt, reicht eine der folgenden Formeln aus:

=IF(MONTH(D1+1)=MONTH(D1),MONTH(D1)-MONTH(C1),MONTH(D1)-MONTH(C1)+1)

=IF(MONTH(D1+1)=MONTH(D1),MONTH(D1)-1,MONTH(D1))-MONTH(C1)+1 =MONTH(EOMONTH(D1,0))-MONTH(EOMONTH(C1,0))+(EOMONTH(D1,0)=D1)

=IF(EOMONTH(D1,0)=D1,MONTH(D1)-MONTH(C1)+1,MONTH(D1)-MONTH(C1))

=IF(D1<EOMONTH(D1,0),MONTH(D1)-1,MONTH(D1))-MONTH(C1)+1 =MONTH(D1)-MONTH(C1)+IF(D1=EOMONTH(D1,0),1,0)

Wenn die Jahre am Anfangs- und Enddatum unterschiedlich sein können, sind die Formeln etwas komplexer. Jede der folgenden Variationen berechnet die korrekte Anzahl von Monatsenden im Datumsbereich:

=12*(YEAR(D1)-YEAR(C1))+MONTH(D1)-MONTH(C1)+INT(D1=EOMONTH(D1,0))

=12*(YEAR(D1)-YEAR(C1))+MONTH(D1)-MONTH(C1)+IF(EOMONTH(D1,0)=D1,1,0)

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+IF(D1=EOMONTH(D1,0),1,0)

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+(D1=EOMONTH(D1,0))

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+(DAY(D1+1)=1)

=MONTH(D1)+12YEAR(D1)-MONTH(C1)-12YEAR(C1)+IF(EOMONTH(D1,0)=D1,1,0)

=(MONTH(D1+1)+12YEAR(D1+1))-(MONTH(C1)+12YEAR(C1))

=IF(D1=EOMONTH(D1,0),1,0)+MONTH(D1)-MONTH(C1)+((YEAR(D1)-YEAR(C1))*12)

=IF(EOMONTH(D1,0)=D1,(MONTH(D1)-MONTH(C1)+1)+(YEAR(D1)-YEAR(C1))12,MONTH(D1)-MONTH(C1)+(YEAR(D1)-YEAR(C1))12)

=IF(D1=EOMONTH(D1,0),DATEDIF(EOMONTH(C1,-1),EOMONTH(D1,0),"m"),DATEDIF(EOMONTH(C1,-1),EOMONTH(D1,0),"m")-1)

=IF(D1<C1,"Error",MONTH(D1)-MONTH(C1)+IF(D1MONTH(D1+1))*1)

=IF(D1<C1,"Error",(YEAR(D1)-YEAR(C1))*12+(MONTH(D1)-MONTH(C1))+(EOMONTH(D1,0)=D1))

=SUM(N(FREQUENCY(EOMONTH(ROW(INDIRECT(C1&":"&D1)),0),EOMONTH(ROW(INDIRECT(C1&":"&D1)),0))>0))-(D1<EOMONTH(D1,0))

Einige dieser Formeln sind etwas lang, daher sollten Sie sorgfältig darauf achten, dass Sie alle erhalten, wenn Sie eine verwenden.

Wenn Sie einen Makroansatz bevorzugen, kann die folgende benutzerdefinierte Funktion von Interesse sein:

Function CountMonthEnds(sd As Date, ed As Date) As Integer     Dim ceom As Integer     Dim cmonth As Integer     Dim lmonth As Integer     Dim x As Date

cmonth = 0     ceom = 0     cmonth = Month(sd)

lmonth = cmonth     ' go to end date + 1 to allow for end date being end of month     For x = sd To ed + 1         cmonth = Month(x)

If cmonth <> lmonth Then             ceom = ceom + 1             lmonth = cmonth         End If     Next x     CountMonthEnds = ceom End Function

Das Makro durchläuft einfach alle Daten zwischen dem Anfangs- und dem Enddatum. Es betrachtet den Monat für jedes der Daten, und wenn es sich ändert, zählt dies als „Monatsende“. Diese Anzahl wird von der Funktion zurückgegeben.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (572) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.