excel - vba - get weeknumber

Question

I would like to get the current week number. I tried this:

MsgBox (Format(Now, "ww", vbMonday))

The result:

3

But it has to be 2 Where is my mistake?

Answer 1

msgbox format(now(),"ww",vbMonday,vbFirstFourDays)

provides the ISO weeknumber, which is what it seems you want.

or:

msgbox worksheetfunction.IsoWeekNum(date)

Note that there is a bug in the VBA Format (and DatePart) functions in that they can return the wrong weeknumber for the last Monday of some years when determining the ISO WeekNumber

So if you are going to use the Format function, you need to check for that bug.

Worksheetfunction.IsoWeekNum does not have that bug.

To workaround the bug, Microsoft suggests checking the weeknumber of the week after any date where the weeknumber calculates to 53.

Function WOY (MyDate As Date) As Integer   ' Week Of Year
  WOY = Format(MyDate, "ww", vbMonday, vbFirstFourDays)
  If WOY > 52 Then
    If Format(MyDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then WOY = 1
  End If
End Function