ISO week number in VBScript or VBA

Question

How can I get the ISO week number of some date in VBScript or VBA?

Answer 1

First, note that:

1. It is important to report the week year along with the week number, as the date's year could be different.
2. Several Windows components contain a bug for some years' last Monday.

In my experience the simplest, clearest and most robust way to compute this is:

Sub WeekNum(someDate, isoWeekYear, isoWeekNumber, isoWeekDay)
  Dim nearestThursday
  isoWeekDay = WeekDay(someDate, vbMonday)
  nearestThursday = DateAdd("d", 4 - Int(isoWeekDay), someDate)
  isoWeekYear = Year(nearestThursday)
  isoWeekNumber = Int((nearestThursday - DateSerial(isoWeekYear, 1, 1)) / 7) + 1
End Sub

This also returns the ISO day of the week, counting from 1 for Mondays.