VBA Convert date to week number

Using VBA, to convert a date into an isoWeeknumber, you merely need the DatePart function (where DT is the date of interest):

isoWeekNumber = DatePart("ww", DT, vbMonday, vbFirstFourDays)

If you want to use other definitions than that specified in ISO 8601, investigate some of the other options for FirstDayOfWeek and FirstWeekOfYear

NOTE

As pointed out by @Mike85, there is a bug in DatePart (and also in the Format) function wherein Monday may be erroneously given a weeknumber of 53 when it should be 1.

There are a variety of workarounds.

In Excel 2013+ (Excel for Mac 2011+) you can use for the ISO Weeknumber:

isoWeekNumber = WorksheetFunction.isoWeekNum(dt)

For earlier versions, you can test the Monday and adjust it if necessary, or you can write a separate routine.

Be carefull when it comes to week numbers as there are different definitions around. The Excel definition differs from the ISO definition. To get the ISO weeknumber use (copied From http://www.rondebruin.nl/win/s8/win001.htm)

Public Function IsoWeekNumber(d As Date) As Integer
    Dim d2 As Long
    d2 = DateSerial(Year(d - Weekday(d - 1) + 4), 1, 3)
    IsoWeekNumber = Int((d - d2 + Weekday(d2) + 5) / 7)
End Function

To make the Week Number with Monday as a first day, use the following:

WorksheetFunction.WeekNum(now, vbMonday)