Wie wandelt man eine Kalenderwoche in ein Datum um – oder umgekehrt – in Excel?
Die Arbeit mit Datumsangaben und Kalenderwochen in Excel ist in der Geschäftsanalyse, Projektplanung und Berichterstattung oft unverzichtbar. Vielleicht möchten Sie beispielsweise wissen, in welche Kalenderwoche ein bestimmtes Datum fällt, oder den zugehörigen Datumsbereich einer Kalenderwoche in einem bestimmten Jahr ermitteln. Excel bietet jedoch keine direkten integrierten Funktionen, um eine Kalenderwoche automatisch in einen vollständigen Datumsbereich umzuwandeln – oder diesen Vorgang schnell umzukehren. Um diese Anforderungen dennoch effizient zu erfüllen, stehen Ihnen je nach Bedarf und Umfang Ihrer Daten verschiedene Formeln, VBA-Lösungen und weitere Excel-Funktionen zur Verfügung. Im Folgenden stellen wir Ihnen mehrere praktische Methoden vor, um diese Aufgabe in Excel souverän zu meistern.
Kalenderwoche mithilfe von Formeln in ein Datum umwandeln
Datum mithilfe von Formeln in Kalenderwoche umwandeln
Umwandlung zwischen Kalenderwoche und Datum mithilfe von VBA-Codes
Kalenderwoche mithilfe von Formeln in ein Datum umwandeln
Angenommen, Sie haben ein bestimmtes Jahr und eine Kalenderwoche in Ihrem Arbeitsblatt eingegeben (z. B.)2015 in Zelle B1 und 15 in Zelle B2). Dann möchten Sie vielleicht das tatsächliche Startdatum (Montag) und Enddatum (Sonntag) dieser Woche berechnen – besonders nützlich für die Terminplanung, wöchentliche Zusammenfassungen oder wöchentliche Berichtszeiträume.
Um das Datumsbereich für die angegebene Kalenderwoche zu berechnen, können Sie die folgenden Excel-Formeln verwenden:
1. Wählen Sie eine leere Zelle zur Anzeige des Startdatums aus (hier Zelle)B5). Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste. Die Formel gibt eine fortlaufende Zahl zurück, die das Datum repräsentiert.
=MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1)2. Geben Sie für das Enddatum derselben Woche (z. B. in Zelle)B6) die folgende Formel ein und drücken Sie anschließend Eingabe. Die Formel gibt die fortlaufende Zahl für den letzten Tag der angegebenen Woche zurück.
=MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7)
Hinweis: In den obigen Formeln enthält B1 das Jahr (z. B. 2015) und B2 die Kalenderwoche, die Sie umwandeln möchten. Passen Sie diese Zellbezüge entsprechend Ihrem tatsächlichen Arbeitsblatt an.
3. Die Formeln liefern zunächst Zahlen statt formatierter Datumsangaben. Um das korrekte Datumsformat anzuzeigen, markieren Sie beide Formelzellen und navigieren Sie zu Start > Zahlenformat-Dropdown-Menü > Kurzes Datum. Dadurch werden die Werte in lesbare Datumsangaben umgewandelt.
Tipps: Diese Formeln basieren auf dem ISO-Wochendatums-System (bei dem Wochen am Montag beginnen), das in europäischen Lohn- und Berichterstattungsstandards üblich ist. Sollte Ihr Unternehmen ein anderes Kalenderwochen-System verwenden, können die Ergebnisse abweichen. Prüfen Sie die Ergebnisse daher stets sorgfältig – insbesondere für Jahre, die mitten in der Woche beginnen (z. B. wenn der 1. Januar kein Montag ist) oder Jahre mit 53 Wochen.
Datum mithilfe von Formeln in Kalenderwoche umwandeln
Umgekehrt möchten Sie vielleicht herausfinden, in welche Kalenderwoche ein bestimmtes Datum fällt. Dafür bietet Excel die WEEKNUM-Funktion. Das ist besonders nützlich bei der Analyse von Arbeitszeiterfassungsdaten, beim Erstellen wöchentlicher Berichte oder wenn Sie Lieferungen und Veranstaltungen wochenweise nachverfolgen möchten.
1. Wählen Sie eine leere Zelle aus, um die Kalenderwoche anzuzeigen. Geben Sie die folgende Formel ein (unter der Annahme, dass sich Ihr Datum in)B1 befindet):
=WEEKNUM(B1,1)2. Drücken Sie anschließend Eingabe. Diese Formel gibt die Kalenderwoche unter Berücksichtigung des Sonntags als ersten Wochentag zurück.
Hinweise:
(1) In dieser Formel enthält B1 die Zelle mit dem Datum, das Sie umwandeln möchten.
(2)Wenn Sie Wochen lieber ab Montag zählen möchten (wie im ISO-Wochensystem üblich), verwenden Sie diese Version der Formel:
=WEEKNUM(B1,2)Umwandlung zwischen Kalenderwoche und Datum mithilfe von VBA-Codes
In diesem Artikel werden zwei VBA-Routinen vorgestellt: eine wandelt eine Kalenderwoche (samt Jahr) in den zugehörigen Datumsbereich um, die andere ermittelt die ISO-Kalenderwoche zu einem beliebigen Datum.
Kalenderwoche in Datumsbereich umwandeln:
1. Öffnen Sie den VBA-Editor, indem Sie auf Entwickler > Visual Basic klicken. Klicken Sie im geöffneten Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
Sub WeekNumberToDateRange()
Dim YearNum As Long
Dim WeekNum As Long
Dim FirstDay As Date, LastDay As Date
Dim Jan4 As Date
YearNum = Application.InputBox("Enter the year:", "KutoolsforExcel", Year(Date), Type:=1)
If YearNum < 1 Then Exit Sub
WeekNum = Application.InputBox("Enter the week number:", "KutoolsforExcel", 1, Type:=1)
If WeekNum < 1 Then Exit Sub
Jan4 = DateSerial(YearNum, 1, 4)
FirstDay = Jan4 - Weekday(Jan4, vbMonday) + 1
FirstDay = FirstDay + (WeekNum - 1) * 7
LastDay = FirstDay + 6
MsgBox "Start date: " & Format(FirstDay, "yyyy-mm-dd") & vbCrLf & _
"End date: " & Format(LastDay, "yyyy-mm-dd"), _
vbInformation, "KutoolsforExcel"
End Sub
2. Führen Sie das Makro mithilfe der 
-Schaltfläche aus. Es fragt Sie nach dem Jahr und der Kalenderwoche und zeigt den entsprechenden Datumsbereich in einem Dialogfeld an.
Datum in Kalenderwoche umwandeln:
1. Kopieren Sie den folgenden VBA-Code, und fügen Sie ihn in das Modul ein:
Sub DateToWeekNumber()
Dim InputDate As Date
Dim WeekNum As Integer
InputDate = Application.InputBox("Enter the date (yyyy-mm-dd):", "KutoolsforExcel", Date, Type:=2)
WeekNum = WorksheetFunction.WeekNum(InputDate, 2)
MsgBox "The week number is: " & WeekNum, vbInformation, "KutoolsforExcel"
End Sub
2. Nachdem Sie diesen Code eingefügt und ausgeführt haben, geben Sie im Aufforderungsdialog Ihr Ziel-Datum ein – das Makro zeigt dann die Kalenderwoche an, wobei Montag als Wochenbeginn gilt. Passen Sie den Code an, indem Sie das zweite Argument in WeekNum auf 1 ändern, um Wochen ab Sonntag zu zählen.
vbMondayoder vbSundayim VBA-Code entsprechend an.Mit einem Klick mehrere nicht standardmäßige Standardformatierung-Datumsangaben in normale Datumsangaben in Excel umwandeln
Die Funktion „Datumsangabe erkennen“ von Kutools für Excel ermöglicht es Ihnen, nicht standardmäßige Datumsangaben, Zahlen (z. B. yyyymmdd) oder Klartext mit nur einem Klick einfach zu identifizieren und in ein standardmäßiges Datumsformat umzuwandeln – für mehr Produktivität und weniger manuelle Fehler. Sichern Sie sich jetzt Ihre 30-tägige kostenlose Testversion mit vollem Funktionsumfang!
Verwandte Artikel:
Wie lässt sich die Anzahl bestimmter Wochentage zwischen zwei Datumsangaben in Excel ermitteln?
Wie addiert oder subtrahiert man Tage, Monate oder Jahre zu einem Datum in Excel?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins