Calculate total of weeks in a specific year - ISO 8601 - VBA Access

Question

Saw this question a few more times but can't seem to get it fixed in my VBA code.

I need to calculate the total number of weeks in a given year comform ISO 8601.

When I use the datediff function: iNumWeeks = DateDiff("ww", "1/1/2015", "31/12/2015", vbMonday, vbFirstJan1) it returns 52 while there are 53 weeks in 2015 (ISO 8601)

How can I get this done?

Answer 1

Here's simple function that will provide the correct result for any year:

Public Function ISO_WeekCount( _
    ByVal datYear As Date) _
    As Byte

' Calculates number of weeks in year of datYear according to the ISO 8601:1988 standard.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, CPH

    Dim bytISO_Thursday As Byte

    ' No special error handling.
    On Error Resume Next

    bytISO_Thursday = Weekday(vbThursday, vbMonday)

    datYear = DateSerial(Year(datYear), 12, 31)
    ' Subtract one week if datYear is in week no. 1 of next year.
    datYear = DateAdd("ww", Weekday(datYear, vbMonday) < bytISO_Thursday, datYear)

    ISO_WeekCount = DatePart("ww", datYear, vbMonday, vbFirstFourDays)

End Function

Answer 2

DatePart, did you try this before

iNumWeeks = DatePart("ww", CDate("31/12/2015"), vbMonday, vbFirstFourDays) '53

Answer 3

If you don't mind using a UDF this code will return it.
=ISOWeekNum("31/12/2015") or =ISOWeekNum(42369) returns 53.

http://www.cpearson.com/excel/DateTimeVBA.htm

'http://www.cpearson.com/excel/DateTimeVBA.htm
'John Green, an Excel MVP from Australia.
Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
    Case Is >= NextYearStart
        ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
        YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
        ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
        YearNum = Year(AnyDate) - 1
    Case Else
        ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
        YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
    ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
    Format(ISOWeekNum, "00"))
End If

End Function

Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If

End Function

Can be used in a query as: SELECT DISTINCT dDate, ISOWeekNum(dDate) FROM tbl_MyTable