weeknum을 VBA로 구현

The formula above assumes that cell A1 contains a valid date for which you want to return the week number. To calculate the correct week number with a user-defined VBA function, you can use the function below: Function WEEKNR(InputDate As Long) As Integer Dim A As Integer, B As Integer, C As Long, D As Integer WEEKNR = 0 If InputDate < 1 Then Exit Function A = Weekday(InputDate, vbSunday) B = Year(InputDate + ((8 - A) Mod 7) - 3) C = DateSerial(B, 1, 1) D = (Weekday(C, vbSunday) + 1) Mod 7 WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1 End Function

Sub test()
    Dim E As Integer
    Dim i As Integer
    E = [a65536].End(xlUp).Row
    For i = 2 To E
        Cells(i, 3) = WEEKNR(Cells(i, 1))
    Next
End Sub

출처 : http://www.exceltip.com