I wrote this tiny code snippet to calculate the number of the week according to the ISO 8601 standard.
While almost surely I did not follow the best algorithm available, the code is easy to understand and arises from a couple of wikipedia articles.
1 Private Function Iso8601WeekNumber(Data As Date) As Integer
2
3 Dim Years53Weeks As String
4 Dim NumOfWeekFstJan As Integer
5 Dim WDiff As Integer
6
7 'here I determine which day of the week is the first of January, see-> http://en.wikipedia.org/wiki/Seven-day_week#Week_numbering
8 If Weekday(CDate("01/01/" & year(Data)), vbMonday) = 1 Or _
9 Weekday(CDate("01/01/" & year(Data)), vbMonday) = 2 Or _
10 Weekday(CDate("01/01/" & year(Data)), vbMonday) = 3 Or _
11 Weekday(CDate("01/01/" & year(Data)), vbMonday) = 4 Then
12 NumOfWeekFstJan = 1
13 ElseIf Weekday(CDate("01/01/" & year(Data)), vbMonday) = 5 Then
14 NumOfWeekFstJan = 53
15 ElseIf Weekday(CDate("01/01/" & year(Data)), vbMonday) = 7 Then
16 NumOfWeekFstJan = 52
17 ElseIf YearHas53Weeks(year(Data) - 1) Then
18 NumOfWeekFstJan = 53
19 Else
20 NumOfWeekFstJan = 52
21 End If
22
23 Iso8601WeekNumber = NumOfWeekFstJan
24
25 ' number of weeks from Data and the first of January
26 WDiff = DateDiff("ww", CDate("01/01/" & year(Data)), Data, vbMonday)
27
28 If WDiff > 0 Then
29 If Iso8601WeekNumber = 1 Then
30 Iso8601WeekNumber = Iso8601WeekNumber + WDiff
31 Else ' first of January is within week # 52 0r 53
32 If WDiff > 0 Then
33 Iso8601WeekNumber = WDiff
34 End If
35 End If
36 End If
37
38 If Iso8601WeekNumber = 53 And Month(Data) > 8 And YearHas53Weeks(year(Data)) = False Then
39 Iso8601WeekNumber = 1
40 End If
41
42 End Function
43
44 Private Function YearHas53Weeks(year As Integer) As Boolean
45
46 YearHas53Weeks = False
47
48 If Weekday(CDate("01/01/" & year), vbMonday) = 4 Then
49 YearHas53Weeks = True
50 ElseIf Weekday(CDate("01/01/" & year), vbMonday) = 3 And isLeapYear(CInt(year)) Then
51 YearHas53Weeks = True
52 End If
53
54 End Function
55
56 'http://www.codeproject.com/Questions/337216/leap-year-problem-in-visual-basic
57 Function isLeapYear(year As Integer)
58 isLeapYear = (year Mod 4 = 0 And year Mod 100 <> 0) Or year Mod 400 = 0
59 End Function
60
In the lines 8-21 the routine determines the week number of the first of January. From Wikipedia we read that if the year begins on Monday,Tuesday, Wednesday or Thursay the first of January is in the week number 1, if it begins on Friday the first of January is in the week number 53, if it begins on Sunday the first of January is in the week number 52 and if it begins on Saturday the first of January is in the week number 52 or 53 depending on how many weeks the year before has got.
The function YearHas53Weeks tell us how many weeks are inside an year. The code simply says :
If the year starts on a Thursday or is a leap year that starts on a Wednesday, that year will have 53 weeks.In the 26-th line I use a VB function to get the number of weeks from the first of January and the date passed as parameter and store the value in the WDiff variable.
Then the code makes some checks, basically it adds WDiff to the week number of the first of January but of course if the 01/01 is on the the 52th or 53th week of the previous year we must add WDiff less 1.
The last check (lines 38-40) states that if we got 53 as the result of the last calculation ad the date that we passed as input is at the end of the year, well that 53 is acceptable only if the year contains 53 week and so, if it this was not the case, the 53-th becomes the first of the next year.