✔ Programação GLOBAL®: VBA Excel: Identificando o número da semana no Ano.

VBA Excel: Identificando o número da semana no Ano.

| Blog Office VBA | Blog Excel | Blog Access |

O código baixo será útil para retornar o número da semana dentro de um ano vigente. Função simples, mas útil... Go ahead!

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

    Let ThisYear = Year(AnyDate)
    Let ThisYearStart = YearStart(ThisYear)
    Let PreviousYearStart = YearStart(ThisYear - 1)
    Let NextYearStart = YearStart(ThisYear + 1)
    
    Select Case AnyDate
        Case Is >= NextYearStart
            Let ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
            Let YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
             Let ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
            Let YearNum = Year(AnyDate) - 1
    Case Else
            Let ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
            Let YearNum = Year(AnyDate)
     End Select

    If IsMissing(WhichFormat) Then Exit Function

    If WhichFormat = 2 Then
        Let ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & Format(ISOWeekNum, "00"))
     End If
End Function

André Luiz Bernardes A&A - WORK, DON´T PLAY! http://al-bernardes.sites.uol.com.br/ bernardess@gmail.com