Weeknumbers using VBA in Microsoft Excel

The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date,
if you are in the U.S. The user defined function below will calculate the correct week number depending
on the national language settings on your computer.

Function UDFWeekNum(InputDate As Date)
    UDFWeekNum = DatePart("ww", InputDate, vbUseSystemDayOfWeek, vbUseSystem)
End Function

The function above can also be modified to calculate the weeknumber the European way:

Function UDFWeekNumISO(InputDate As Date)
    UDFWeekNumISO = DatePart("ww", InputDate, vbMonday, vbFirstFourDays)
End Function

The two functions above can, due to a bug, return a wrong week number. This occurs for dates around
New Year for some years, e.g. the years 1907, 1919, 1991, 2003, 2007, 2019 and 2091. You can use this
worksheet formula to calculate the correct week number (Thanks to George Simms, [email protected],
for pointing this out):

=INT((A1-(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))-3+ MOD(WEEKDAY(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))+1,7))/7)+1

The formula above assumes that cell A1 contains a valid date for which you want to return the week number.
To calculate the correct week number with a user-defined VBA function, you can use the function below:

Function WEEKNR(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
    WEEKNR = 0
    If InputDate < 1 Then Exit Function
    A = Weekday(InputDate, vbSunday)
    B = Year(InputDate + ((8 - A) Mod 7) - 3)
    C = DateSerial(B, 1, 1)
    D = (Weekday(C, vbSunday) + 1) Mod 7
    WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1
End Function

One thought on “Weeknumbers using VBA in Microsoft Excel

Leave a Reply

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

5 × = forty

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>