You can use the following basic syntax in VBA to convert a date to a week number:

Sub FindWeekNumber()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.WeekNum(Range("A" & i))
    Next i
    
End Sub

This particular example will find the week number for each date in the range A2:A9 and display the results in the range B2:B9.

Note that the VBA WeekNum method assumes that weeks start on Sundays.

To specify a different day as the start of the week, you can add a second argument to the WeekNum method:

Sub FindWeekNumber()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.WeekNum(Range("A" & i), vbMonday)
    Next i
    
End Sub

The following example shows how to use the WeekNum method to convert dates to week numbers in practice.

Example: Convert Date to Week Number in VBA

Suppose we have the following column of dates in Excel:

Suppose we would like to convert each date to a week number and display the week number in column B.

We can create the following macro to do so:

Sub FindWeekNumber()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.WeekNum(Range("A" & i))
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B displays the week number for each date in column B, assuming that the weeks start on Sundays.

If you’d like to specify a different day as the start of the week, such as Monday, you can use the following syntax:

Sub FindWeekNumber()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.WeekNum(Range("A" & i), vbMonday)
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B now displays the week number for each date in column B, assuming that the weeks start on Mondays.

Note: You can find the complete documentation for the VBA WeekNum method .