General discussion

  • Creator
    Topic
  • #2226388

    Please help me run this script in excel.

    Locked

    by kamlesh_waghela2000 ·

    Public 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 Integer

    ThisYear = 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

All Comments

Viewing 1 reply thread