MS Access get ISO standard week number

Question

I'm surprised that I can't find any existing solutions to this online but I just need an SQL function that returns an ISO standard week number (i.e. the start of week 1 is always the first Monday of the year).

None of the DatePart function options consistently return the correct result. I had thought the option "vbFirstFourDays - Start with the first week that has at least four days in the new year." but testing it for today (12th Jan) returns week 3, not week 2 (my expression is DatePart("ww",Now(),2) )

This year ISO week 1 starts on 4th Jan, next Year the 2nd Jan and last year it was the 5th of Jan.

Many thanks

Answer 1

The DatePart function does indeed calculate the ISO-8601 week number almost^* correctly when it uses vbMonday for the firstdayofweek argument and vbFirstFourDays for the firstweekofyear argument, e.g.,

DatePart("ww", Date(), vbMonday, vbFirstFourDays)

or, when used directly in an Access query

DatePart("ww", Date(), 2, 2)

^* Note that the bug documented here has apparently never been fixed, so the following Mondays in the 21^st century are reported as being in week 53 when according to ISO-8601 they should be in week 1 of the following year:

2003-12-29
2007-12-31
2019-12-30
2031-12-29
2035-12-31
2047-12-30
2059-12-29
2063-12-31
2075-12-30
2087-12-29
2091-12-31

Answer 2

Just to follow on from Gord Thompson, Microsoft have provided a workaround which returns the correct ISO week in all circumstances. It simply changes week 53 to week 1. Simply place this in a VBA Module and then you'll be able to use the function in Excel/Access.

Public Function ISOWeek(MyDate As Date) As Integer

      ISOWeek = Format(MyDate, "ww", vbMonday, vbFirstFourDays)
      If ISOWeek > 52 Then
         If Format(MyDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then ISOWeek = 1
      End If

End Function

Answer 3

There are more problems with the ISO weeknumbers than just the 2 week digits returned by DatePart.

January 1st on a Friday should be in week 53 of the previous year

December 31 on a Monday should be in week 1 of the next year

A lot of businesses in Europe use a four digit number to show year and week together. In those cases:

Friday #01/01/2021# should be shown as week "2153"

Monday #12/31/2018# should be shown as week "1901"

I have created 2 wrappers around the DatePart function to add the correct year and show the right weeknumber in case DatePart is in error.

Public Function ISO_YYWW(dat As Date) As String ' ISO 8601 / NEN 2772
    Dim ww As Integer
    ww = CInt(ISO_WW(dat))
    If ww >= 52 And Val(Format(dat, "ww")) <= 2 Then
       ISO_YYWW = Format(((Year(dat) - 1) Mod 100), "00") & Format(ww, "00")
    ElseIf ww = 1 And Month(dat) = 12 Then
       ISO_YYWW = Format(((Year(dat) + 1) Mod 100), "00") & Format(ww, "00")
    Else
       ISO_YYWW = Format(dat, "YY") & Format(ww, "00")
    End If
End Function
Public Function ISO_WW(dat As Date) As String ' ISO 8601 / NEN 2772
    If Format(dat, "DD-MM") = "31-12" And DatePart("W", dat, vbMonday, vbFirstFourDays) = 1 Then ' 31-dec on a monday
       ISO_WW = "01"
    ElseIf Format(dat, "DD-MM") = "30-12" And DatePart("W", dat, vbMonday, vbFirstFourDays) = 1 Then ' 30-dec on a monday
       ISO_WW = "01"
    ElseIf Format(dat, "DD-MM") = "29-12" And DatePart("W", dat, vbMonday, vbFirstFourDays) = 1 Then ' 29-dec on a monday
       ISO_WW = "01"
    Else
       ISO_WW = Format(DatePart("ww", dat, 2, 2), "00")
    End If
End Function

I have tested from 1970 to 2021 and found no problems using this code

Sub test()
    Dim dat As Date, yy As Integer, f As Integer
    f = FreeFile
    Open CodeDb.Name & ".txt" For Output As #f
    Print #f, "date", "day", "ISO_WW / DOW", "ISO_YYWW"
    For yy = 1970 To 2021
        For dat = CDate("31/12/" & yy) - 7 To CDate("31/12/" & yy) + 7
            If ISO_WW(dat) >= 52 Or ISO_WW(dat) = "53" Or Val(ISO_WW(dat)) = 1 Then
               Print #f, Format(dat, "yyyy-mm-dd"), Format(dat, "DDD"), ISO_WW(dat) & " / " & DatePart("W", dat, vbMonday, vbFirstFourDays), ISO_YYWW(dat)
            End If
        Next dat
        Print #f, ""
    Next yy
    Close #f
End Sub