General discussion
-
Topic
-
Please help me run this script in excel.
LockedPublic Function ISOWeekNum(AnyDate As Date, _
Optional WhichFormat As Variant) As Integer
‘
‘ WhichFormat: missing or <> 2 then returns week number,
‘ = 2 then YYWW
‘
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As IntegerThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear – 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate – NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function