Sie wissen wahrscheinlich, dass Sie die WEEKNUM-Funktion verwenden können, um die Wochennummer in einem Jahr für ein bestimmtes Datum zurückzugeben. Was ist, wenn Sie das Gegenteil tun möchten – um den letzten Tag einer bestimmten Woche zu bestimmen, wenn Sie nur die Wochennummer innerhalb des Jahres haben? Wenn Sie beispielsweise Woche 37 angeben, möchten Sie das Datum des letzten Tages in dieser bestimmten Woche ermitteln.

Es gibt keine integrierte Funktion, um das gewünschte Datum herauszufinden, aber es gibt eine Reihe von Möglichkeiten, wie Sie sich dem Problem nähern und es herausfinden können. In diesen Beispielen nehmen wir an, dass sich das Jahr in Zelle A1 und die gewünschte Wochenzahl in Zelle B1 befindet.

Der erste Ansatz besteht darin, den ersten Tag des gewünschten Jahres zu berechnen, sieben Tage für jede der Wochen zu addieren und dann die Wochentagszahl für den ersten Tag des Jahres zu subtrahieren.

=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))

Diese Formel gibt ein Datum zurück, das immer den letzten Tag der Woche darstellt, wenn die Woche an einem Samstag endet. Wenn Sie möchten, dass die Woche an einem anderen Wochentag endet, wird die Formel komplexer. Wenn Sie möchten, dass die Woche an einem Sonntag endet, müssen Sie einfach 1 zur Formel hinzufügen:

=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))+1

Wenn Sie dieselbe Logik anwenden, könnten Sie denken, Sie könnten die am Freitag endenden Wochen herausfinden, indem Sie einfach 1 von der Formel abziehen. Dies ist nicht der Fall, da Sie den Jahresanfang als Basis verwenden.

Wenn Sie 1 subtrahieren, stoßen Sie auf das Problem, dass der 1. Januar eines jeden Jahres ein Samstag ist. Wenn Sie 1 abziehen, erhalten Sie nicht den ersten Freitag des Jahres, sondern den 31. Dezember des Vorjahres. Um die am Freitag endenden Wochen zu berechnen, benötigen Sie eine viel komplexere Formel:

=DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))



((6-WEEKDAY(DATE(A1,1,1))<0)7)+1)+((B1-1)7)

Da einige Jahre 52 Wochen und einige 53 Wochen haben – wiederum abhängig davon, ob die Wochen am Freitag, Samstag oder Sonntag enden – ist es eine gute Idee, die Formeln so zu ändern, dass überprüft wird, ob das Rückgabedatum innerhalb desselben Jahres liegt dass Sie analysieren. Wenn Sie dies nicht überprüfen, geben die bisher bereitgestellten Formeln gerne Daten für Woche 73, Woche 89 oder Woche 123 eines bestimmten Jahres zurück – sie passen das Datum einfach an das entsprechende zukünftige Jahr an.

Hier ist die Formel für Wochen, die am Freitag enden:

=IF(YEAR(DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))



((6-WEEKDAY(DATE(A1,1,1))<0)7)+1)+((A1-1)7))=A1, DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))+((6-WEEKDAY(DATE( A1,1,1))<0)7)+1)+((B1-1)7),"")

Wenn das berechnete Datum nicht im selben Jahr liegt wie in Zelle A1 angegeben, gibt die Formel nichts zurück. Hier ist die Formel für Wochen, die am Samstag enden:

=IF(YEAR(DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1)))=A1, DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1)),"")

Schließlich ist hier die Formel für Wochen, die am Sonntag enden:

=IF(YEAR(DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1))+1)=A1, DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1))+1,"")

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

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

Berechnung des letzten Tages in einer Wochennummer.