Kathy benötigt eine Formel, die jedes Datum innerhalb des aktuellen Monats auf den letzten Tag des Vormonats ändert. Sie merkt auch an, dass es hilfreich wäre, ein VBA-Makro zu haben, das dies mit den Daten in einer Reihe ausgewählter Zellen tun könnte.

Es gibt tatsächlich verschiedene formelhafte Ansätze, mit denen Sie das gewünschte Datum ableiten können. Diese beiden sind die kürzesten und am einfachsten zu verwendenden:

=A1-DAY(A1)

=EOMONTH(A1,-1)

Der erste funktioniert, weil „Tag 0“ des aktuellen Monats immer als letzter Tag des vorherigen Monats betrachtet wird. Wenn das Datum in Zelle A1 also 25.09.20 ist, subtrahieren Sie am Ende 25 (den TAG-Wert) von diesem Tag, was ein Datum von 20.09.20 oder 31.08.20 ergibt.

Die zweite Formel funktioniert aufgrund der Einbeziehung des zweiten Parameters für die EOMONTH-Funktion. Dieser Parameter gibt an, wie viele Monate in der Vergangenheit (negativ) oder in der Zukunft (positiv) das Monatsende berechnet werden soll.

In beiden Fällen müssen Sie möglicherweise die Zelle mit der Formel als Datum formatieren. Wenn Sie dies nicht tun, wird möglicherweise die zugrunde liegende Seriennummer angezeigt, die von Excel zur Berechnung der Daten verwendet wird.

Wenn Sie ein Makro möchten, das tatsächlich Daten in einem ausgewählten Bereich in den letzten Tag des Vormonats konvertiert, reicht das Folgende aus.

Sub LastDayPreviousMonth()

Dim rCell As Range

For Each rCell In Selection         If IsDate(rCell) And Not rCell.HasFormula Then             rCell.Value = WorksheetFunction.EoMonth(rCell.Value, -1)

End If     Next rCell End Sub

Das Makro durchläuft jede Zelle im ausgewählten Bereich und prüft, ob sie ein Datum enthält und keine Formel ist. (Mit anderen Worten, wenn das Datum in einer Zelle das Ergebnis einer Formel ist, wird die Formel nicht geändert.)

Die EOMONTH-Funktion wird dann wie zuvor beschrieben verwendet, um das gewünschte Datum zu berechnen.

Es sollte erwähnt werden, dass der Makroansatz auch Textdaten konvertiert. Wenn eine Zelle beispielsweise als Text das Datum „15.06.20“ enthält,

dann ändert das Makro es auf 30.05.20. Sobald das angepasste Datum wieder in die Zelle eingegeben wurde, wird es als Datums- / Uhrzeit-Seriennummer und nicht als Text angezeigt.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

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

Dieser Tipp (13304) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.