Definition gemäss DIN 1355:
Die deutschen Kalenderwochen berechnen sich wie folgt:
Der 1. Januar eines Jahres gehört erst dann zur ersten Kalenderwoche, wenn dieser Tag auf einen Montag, Dienstag, Mittwoch oder Donnerstag fällt.
Falls der 1. Januar ein Freitag, Samstag oder Sonntag ist, zählt er, und ggf. auch der 2. und 3. Januar, noch zur letzten Kalenderwoche des vorherigen Jahres.
Weiterhin können der 29., 30. und 31.12. eines Jahres schon zur Kalenderwoche 1 des neuen Jahres gehören.
Das ist genau dann der Fall, wenn der 31.12. auf einen Montag, Dienstag oder Mittwoch fällt.

Option Explicit
 
Public Sub Test_KW()
 Dim chkDate As Date
 On Error GoTo Fehler
 chkDate = InputBox("Bitte geben Sie ein Datum in dieser Form ein:", _
  "DIN KW Berechnung", "1.1.2007")
 If IsEmpty(chkDate) Then Exit Sub
 If Not IsDate(chkDate) Then
  MsgBox "Ungültiges Datum"
  Exit Sub
 End If
 MsgBox "Die DIN KW für den " & Chr$(13) & _
  chkDate & Chr$(13) & "lautet: " & DIN_KW(chkDate)
 Exit Sub
 Fehler:
 MsgBox Err.Description
End Sub
 
Public Function DIN_KW(DasDatum As Date) As Byte
 Dim KW As Date
 KW = 4 + DasDatum - Weekday(DasDatum, 2)
 DIN_KW = (KW - DateSerial(Year(KW), 1, -6)) \ 7
End Function

Hier ein Beispiel wie die Funktion als "Benutzerdefinierte Funktion" in EXCEL angewendet wird.

Speichern Sie den Code entweder in einem Modul Ihrer Arbeitsmappe, oder speichern Sie
diese Funktion in der persönlichen Arbeitsmappe "PERSONL.XLS". Dann steht Ihnen die
Funktion jederzeit zur Verfügung.

Mit der Funktion "DateSerial(Year(KW), 1, -6)" wird der 1.1. des
Jahres durch das in KW berechneten Datums bestimmt,... -6 Tage !!
-6 deshalb um eine allfälliges Datum gemäss DIN 1355 zu berechnen.

Daraus ergibt sich in unserem Beispiel der Wert 371.
Ein normales Jahr hat jedoch maximal 365/366 Tage

Der ermittelte Wert wird also durch 7 (Anzahl Wochentage) dividiert und wir erhalten die
Ganzzahl ("Dim KW as Date") zurück als Wochennummer

DIN_KW = (KW - DateSerial(Year(KW), 1, -6)) / 7

In unserem Fall korrekterweise die Woche 53,... und nicht 1, wie EXCEL Ihnen
versucht darzustellen.