Bei der Entwicklung eines Arbeitsblatts müssen Sie möglicherweise den letzten Geschäftstag eines bestimmten Monats kennen. Angenommen, Ihre Geschäftstage laufen von Montag bis Freitag, gibt die folgende Formel das gewünschte Datum zurück:

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY (DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Diese Formel gibt ein Datum zurück, das nur Montag bis Freitag ist, und immer den letzten solchen Tag im Monat, der durch das Datum in A1 dargestellt wird. Für einige Zwecke müssen Sie möglicherweise wissen, was der letzte Freitag eines bestimmten Monats ist. Dies lässt sich leicht mit folgender Formel feststellen:

=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))+(WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))>5)*7-1

Diese Formel berechnet den letzten Tag des Monats für das Datum in Zelle A1 und subtrahiert basierend auf dem Wochentag dieses Datums die entsprechende Anzahl von Tagen, um den vorherigen Freitag zurückzugeben.

Wenn Sie Geschäftsferien berücksichtigen möchten, wird die Komplexität der Formel sehr schnell sehr hoch. Aus diesem Grund ist es am besten, eine benutzerdefinierte Funktion (ein Makro) zu erstellen, die den letzten Geschäftstag ermittelt und Feiertage kompensiert.

Das folgende Makro gibt ein Datum von Montag bis Freitag zurück, das den letzten Geschäftstag darstellt. Das Datum wird mit einer Feiertagsliste (HolidayList) verglichen, die ein benannter Bereich in Ihrer Arbeitsmappe sein sollte. Wenn sich herausstellt, dass das Datum ein Feiertag ist, wird der Endgeschäftstag verringert, bis ein geeigneter Tag gefunden wurde.

Function LastWorkDay(lRawDate As Long, _     Optional rHolidayList As Range, _     Optional bFriday As Boolean = False) As Long

LastWorkDay = DateSerial(Year(lRawDate), _       Month(lRawDate) + 1, 0) - 0     If bFriday Then         LastWorkDay = MakeItFriday(LastWorkDay)

Else         LastWorkDay = NoWeekends(LastWorkDay)

End If

If Not rHolidayList Is Nothing Then         Do Until myMatch(LastWorkDay, rHolidayList) = 0             LastWorkDay = LastWorkDay - 1             If bFriday Then                 LastWorkDay = MakeItFriday(LastWorkDay)

Else                 LastWorkDay = NoWeekends(LastWorkDay)

End If         Loop     End If End Function
Private Function myMatch(vValue, rng As Range) As Long     myMatch = 0     On Error Resume Next     myMatch = Application.WorksheetFunction _         .Match(vValue, rng, 0)

On Error GoTo 0 End Function
Private Function NoWeekends(lLastDay As Long) As Long     NoWeekends = lLastDay     If Weekday(lLastDay) = vbSunday Then _       NoWeekends = NoWeekends - 2     If Weekday(lLastDay) = vbSaturday Then _       NoWeekends = NoWeekends - 1 End Function
Private Function MakeItFriday(lLastDay As Long) As Long     MakeItFriday = lLastDay     While Weekday(MakeItFriday) <> vbFriday         MakeItFriday = MakeItFriday - 1     Wend End Function

Beachten Sie, dass drei private Funktionen enthalten sind. Diese Funktionen werden in der Hauptfunktion LastWorkDay aufgerufen. Der erste, myMatch, ist ein „Wrapper“ für die reguläre Match-Methode. Diese Verwendung ist aufgrund der erforderlichen Fehlerbehandlung enthalten.

Die zweite Funktion, NoWeekdends, wird verwendet, um ein Datum bis zum vorherigen Freitag zu sichern, wenn es sich zufällig um einen Samstag oder Sonntag handelt. Die MakeItFriday-Funktion wird verwendet, um sicherzustellen, dass ein Datum immer ein Freitag ist.

Um diese benutzerdefinierte Funktion aus Ihrem Arbeitsblatt zu verwenden, verwenden Sie sie in einer Formel wie der folgenden:

=LastWorkDay(A1, HolidayList, TRUE)

Der erste Parameter (A1) ist das auszuwertende Datum. Der zweite Parameter (HolidayList) ist eine optionale Liste von Feiertagsdaten. Wie hier gezeigt, wird davon ausgegangen, dass HolidayList ein benannter Bereich im Arbeitsblatt ist.

Wenn dieser Parameter angegeben ist, stellt die Funktion sicher, dass ein zurückgegebenes Datum nicht in der Liste der Daten in HolidayList enthalten ist.

Der letzte Parameter ist ebenfalls optional. es kann entweder WAHR oder FALSCH sein.

(Der Standardwert ist, falls nicht angegeben, FALSE.) Wenn dieser Parameter auf TRUE gesetzt ist, gibt die Funktion immer den letzten Freitag des Monats zurück. Wenn dieser Parameter TRUE ist und die HolidayList bereitgestellt wird, gibt die Funktion den letzten Nicht-Feiertags-Freitag des Monats zurück.

_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 (10085) gilt für Microsoft Excel 2007 und 2010. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Der letzte Geschäftstag.