Anita hat ein Arbeitsblatt mit über 10.000 Zeilen. Jede Zeile repräsentiert einen individuellen Vertrag. Jeder Vertrag hat ein Startdatum (Spalte A) und ein Enddatum (Spalte B). Sie braucht eine Möglichkeit, in den Spalten C, D, E usw. aufzulisten.

die einzelnen vertraglich vereinbarten Monate. Wenn beispielsweise Spalte A den 1. Juli 2017 und Spalte B den 30. September 2017 enthält, muss Spalte C den 17. Juli, Spalte D den 17. August und Spalte E den 17. September enthalten. Welche Formeln auch immer verwendet werden, muss den Vertrag berücksichtigen Längen, die sehr unterschiedlich sein können.

Es gibt verschiedene Möglichkeiten, eine solche Aufgabe zu erledigen. Es ist möglich, einige Zwischenspalten zu verwenden, in denen beispielsweise der Startmonat und die Anzahl der Monate jedes Vertrags angegeben sind. Es stellt sich jedoch heraus, dass solche Zwischenspalten nicht erforderlich sind. Angenommen, Ihre Verträge beginnen in Zeile 2, könnten Sie die folgende Formel in Zelle C2 einfügen:

=TEXT(A2,"mmm yy")

Ab Zelle D2 können Sie dann die folgende Formel einfügen:

=IF(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2), DAY($A2))>$B2,"",TEXT(DATE(YEAR($A2),MONTH($A2)

+COLUMNS($D2:D2),DAY($A2)),"mmm yy"))

Dies ist eine Formel, die die Position der Zellen, die die Formel enthalten, zusammen mit dem Vertragsstartdatum verwendet, um im Grunde den Monat des Versatzes von diesem Startmonat zu berechnen. Die Formel kann so weit nach rechts kopiert werden (Spalten E, F, G usw.), dass alle Monate und Jahre angezeigt werden.

Der einzige Nachteil der Formel besteht darin, dass der letzte Monat nicht angezeigt wird, wenn das Vertragsenddatum früher im Monat als das Vertragsbeginndatum liegt. Wenn der Vertrag am 12. Juni 2017 beginnt und am 05. Februar 2018 endet, wird der letzte Monat (Februar) in der Formel nicht angezeigt. Sie können stattdessen die folgende Formel in Zelle C2 verwenden und sie so weit wie nötig nach rechts kopieren:

=IF(DATE(YEAR($A2),MONTH($A2)+COLUMN()-2,0)

>DATE(YEAR($A2),MONTH($B2)+1,0),"",TEXT(DATE (YEAR($A2),MONTH($A2)+COLUMN()-2,0),"mmm yy"))

Diese Formel basiert auch auf den Spalten, in denen sie sich befindet, und berechnet daraus einen Versatz vom Vertragsbeginn. Die Formel funktioniert einwandfrei, unabhängig von der Beziehung zwischen Vertragsbeginn und -enddatum.

Wenn Sie sich nicht auf die Spaltenpositionierung verlassen möchten, können Sie einen anderen Ansatz wählen. Fügen Sie die folgende Formel in Zelle C2 ein:

=(A2)

Geben Sie dann in Zelle D2 die folgende Formel ein:

=IF($B2>C2,EOMONTH(C2,1),"")

Kopieren Sie die Formel in D2 so viele Zellen wie nötig nach rechts und formatieren Sie dann alle diese Zellen (einschließlich C2 und D2) als Datumsangaben, die nur den Monat und das Jahr anzeigen. Die Formel überprüft B2 (das Vertragsenddatum), um festzustellen, ob es größer als C2 ist. Wenn dies der Fall ist, gibt die Formel die Seriennummer des letzten Tages des nächsten Monats in Serie zurück. Ist dies nicht der Fall, wird ein Leerzeichen („“) zurückgegeben.

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

Dieser Tipp (12584) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Bestimmen von Monatsnamen für einen Datumsbereich.