Getting Week Numbers in Access

It’s always a little embarrassing when you roll your own Function to achieve some functionality that already exists in Excel or Access. 

Recently, I stumbled across a user-defined function designed to find a week number in Access.  I asked the owner of this code why he wrote a function to do this when Access has one.  He gave me the ‘thousand-yard stare’, then he went on to explain that he couldn’t find the WEEKNUM function in Access.

In Excel, the WEEKNUM function converts a given date into a week number.  For example, this would give you the week number in which the current date falls. 


There is no such function in Access.  You won’t find WEEKNUM in the list of available Access Functions.  There’s is WEEKDAY and WEEKDAYNAME, but no WEEKNUM.

So he naturally started to write his own function. 

Well, it turns out you can get to a week number in Access  by using the DATEPART function.  Like so:


You can also use this in Excel VBA to get around calling Application.WorksheetFunction.WeekNum

Today, my friend still uses his own Week Number user-defined function  – mainly out of spite. I suggested that he create a function that returns the average of range of numbers.  He told me to shut up.

Have you ever rolled your own user-defined function only to find out that the functionality already existed?

15 thoughts on “Getting Week Numbers in Access

  1. Ron de Bruin

    Submiting this test procedure to prove out the bug:


    Sub Test2()
    Dim GL_Dt As Date
    Dim I As Long

    For I = 39814 To 100000 ‘ test for more then 27 years
    GL_Dt = I
    If Int(([GL_Dt] – DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3) + Weekday(DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3)) + 5) / 7) _
    <> DatePart(“ww”, [GL_Dt], vbMonday, vbFirstFourDays) Then MsgBox “Error in date ” & I
    Next I
    End Sub

    Public Function IsoWeekNumber(d1 As Date) As Integer
    ‘ Attributed to Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 – Weekday(d1 – 1) + 4), 1, 3)
    IsoWeekNumber = Int((d1 – d2 + Weekday(d2) + 5) / 7)
    End Function


  2. Jan-Willem Borst

    I can hardly call this a PowerTip. A good function in Access for ISO (european) weeknumbers and the year that goes with it, that would be a PowerTip.

  3. cincin


    sorry for the novice question, but to which area is the format applied?
    In Access:


    Format ([YourField],”ww”)


    thank you

  4. ollie krapf

    Excellent ideas ! I was fascinated by the information ! Does anyone know where my assistant might be able to access a fillable IRS W-9 version to type on ?

  5. roticanai

    Adding “2” to its 4th argument solved the problem. My machine seems on System2.

    Apolotize for the dumb question in a smart forum

Leave a Reply

Your email address will not be published. Required fields are marked *