Harold versucht, eine Formel zu entwickeln, die ihm die letzten fünf Jahre angibt, in denen ein bestimmtes Datum (z. B. der 10. Mai) an einem bestimmten Wochentag (z. B. Donnerstag) aufgetreten ist.

Es gibt verschiedene Möglichkeiten, diese Informationen zu ermitteln. Eine einfache Möglichkeit besteht darin, in ein neues Arbeitsblatt das zu testende Datum (z. B. den 10.05.18) in Zelle A1 einzugeben. Geben Sie direkt darunter in Zelle A2 ein Datum ein Jahr zuvor ein: 10.05.17. Wählen Sie diese beiden Zellen aus und ziehen Sie den Füllpunkt für beliebig viele Zellen nach unten. Am Ende wird eine Spalte mit dem Datum 10. Mai für absteigende Jahre angezeigt.

Geben Sie als nächstes in Zelle B1 die folgende Formel ein:

=WEEKDAY(A1)

Kopieren Sie diese Formel für so viele Zeilen, wie Sie Daten in Spalte A haben.

Dies gibt einen Wert von 1 bis 7 zurück, der den Wochentag für die Daten in Spalte A darstellt. Da 1 = Sonntag, 2 = Montag usw., bedeutet dies, dass jeder Wert von 5 einen Donnerstag darstellt. Sie können diese Formel also in Zelle C1 einfügen und nach unten kopieren:

=IF(B1=5,YEAR(A1),"")

Dies „ruft“ die Jahre auf, in denen das Datum in Spalte A ein Donnerstag ist.

Wenn Sie möchten, können Sie einfach auf das Hinzufügen von Spalte C verzichten und stattdessen die Filterung verwenden, um nur die Daten anzuzeigen, an denen der Wert in Spalte B 5 (Donnerstag) beträgt.

Sie können auch eine Array-Formel verwenden, um die gewünschten Informationen zu finden.

Wählen Sie zunächst fünf Zellen in einer beliebigen Spalte aus. Geben Sie dann Folgendes in die Formelleiste ein:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),5,10))=5)*ROW(1918:2018),ROW(1:5))

Geben Sie die Formel ein, indem Sie Strg + Umschalt + Eingabetaste drücken. Das Ergebnis ist, dass die fünf Zellen die letzten fünf Jahre enthalten werden, in denen der 10. Mai ein Donnerstag war. Sie können die Formel vielseitiger gestalten, indem Sie die Schlüsselelemente auf folgende Weise durch benannte Bereiche ersetzen:

=LARGE((WEEKDAY(DATE(ROW(1918:2018),MO,DA))=DW)*ROW(1918:2018),ROW(1:5))

Jetzt müssen Sie nur noch die benannten Bereiche MO (Monat), DA (Tag) und DW (Wochentag) erstellen. Wenn Sie die Werte in diesen benannten Bereichen ändern, aktualisiert die Array-Formel die fünf Zellen, um die gewünschten Jahre wiederzugeben.

Eine dritte Möglichkeit, die Informationen zu finden, besteht darin, ein Makro zu erstellen, das nach einem Startdatum und einem Wochentag fragt. Das Makro kann dann jedes Jahr einen Schritt zurücktreten, bis es fünf Jahre erreicht, die die Kriterien erfüllen.

Sub CalcDates()

Dim sTemp As String     Dim dBegin As Date     Dim dWork As Date     Dim J As Integer     Dim iDoW As Integer     Dim iYears(5) As Integer

sTemp = InputBox("Beginning Date?")

dBegin = CDate(sTemp)

If dBegin > 0 Then         sTemp = InputBox("Day of Week?")

sTemp = LCase(Trim(sTemp))

iDoW = 0         For J = 1 To 7             If sTemp = LCase(WeekdayName(J)) Then iDoW = J         Next J         If iDoW > 0 Then             dWork = dBegin             J = 0             While J < 5                 If Weekday(dWork) = iDoW Then                     J = J + 1                     iYears(J) = Year(dWork)

End If                 dWork = DateAdd("yyyy", -1, dWork)

Wend

sTemp = "These are the previous five years in which "

sTemp = sTemp & MonthName(Month(dBegin)) & " " & Day(dBegin)

sTemp = sTemp & " falls on a " & WeekdayName(iDoW) & ":"

sTemp = sTemp & vbCrLf & vbCrLf             For J = 5 To 1 Step -1                 sTemp = sTemp & iYears(J) & vbCrLf             Next J             MsgBox sTemp         End If     End If End Sub

Wenn das Makro die fünf Jahre findet, werden sie in einem Meldungsfeld angezeigt.

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

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