weeknum을 VBA로 구현

2진법세상

weeknum을 VBA로 구현

최영명 2008. 3. 2. 13:13

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