' source: http://www.excelformeln.de/formeln.html?welcher=7
' USA: week containing 1st of january
' Germany:
' - Before: there was no defined calendar week
' - Then: week starts on monday
' 1976: DIN 1355: CW-01 :=
' - first week of year containing > 3 days
' - simplified: week containing 04th of january
' 1988: international Norm ISO 8601
' 1992: EU Norm EN 28601
' 1993: Germany DIN EN 28601
'
Public Function calendarWeek(d As Date) As Integer
' DE: calendarWeek = KÜRZEN((D6-DATUM(JAHR(D6+3-REST(D6-2;7));1;REST(D6-2;7)-9))/7)
' US: calendarWeek = INT((D6-DATE(YEAR(D6+3-MOD(D6-2;7));1;MOD(D6-2;7)-9))/7)
Const DAYS_PER_WEEK As Double = 7#
Const MIN_DAYS_IN_WEEK_1 As Integer = 4
Dim rest As Integer
rest = (d - 2) Mod DAYS_PER_WEEK ' ok
Dim y As Integer
y = year(d + MIN_DAYS_IN_WEEK_1 - 1 - rest) ' ok
Dim day As Integer
day = rest - 9
calendarWeek = Int((d - DateSerial(y, 1, day)) / DAYS_PER_WEEK)
End Function