Create date range in Google Sheets from calendar week (with 00:00 on the first date and 23:59 on the end date)

Formulas.md

Formula start date (year 2021 hardcoded in my case)

=TEXT(DATE(2021; 1; -3 + 7 * SPLIT( LOWER($A2) ; "abcdefghijklmnopqrstuvwxyz " ) - WEEKDAY(DATE(2021; 1; 4); 2) + 1); "dd.mm.yyyy 00:00")

Formula end date (year 2021 hardcoded in my case)

=TEXT(DATE(2021; 1; -3 + 7 + 7 * SPLIT( LOWER($A2) ; "abcdefghijklmnopqrstuvwxyz " ) - WEEKDAY(DATE(2021; 1; 4); 2)); "dd.mm.yyyy 23:59")

Note

Not all would be necessary just for the date range, but I wanted to write "CW XYZ" and needed the time as well for some calculation

Sources

• Get date from calendar week: https://weeknumber.net/how-to/google-sheets#:~:text=How%20to%20get%20the%20date,number%20(1%2D53).
• Extract number from text: https://www.extendoffice.com/documents/excel/4710-google-sheets-extract-number-from-cell.html
• Format date with time: https://stackoverflow.com/a/58971600

——————————————————

In German

Datumsbereich in Google Tabellen von Kalenderwoche erstellen (mit 00:00 beim Startdatum und 23:59 am Enddatum)

Formel Startdatum (Jahr 2021 in meinem Fall direkt in die Formel geschrieben)

=TEXT(DATUM(2021; 1; -3 + 7 * SPLIT( KLEIN($A2) ; "abcdefghijklmnopqrstuvwxyz " ) - WOCHENTAG(DATUM(2021; 1; 4); 2) + 1); "dd.mm.yyyy 00:00")

Formel Enddatum (Jahr 2021 in meinem Fall direkt in die Formel geschrieben)

=TEXT(DATUM(2021; 1; -3 + 7 + 7 * SPLIT( KLEIN($A2) ; "abcdefghijklmnopqrstuvwxyz " ) - WOCHENTAG(DATUM(2021; 1; 4); 2)); "dd.mm.yyyy 23:59")