ERLANDSEN DATA CONSULTING Excel & VBA Tips

# Weeknumbers

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:

```=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
```

Document last updated 2001-03-14 12:44:38      Printerfriendly version

 Eric from Netherlands wrote (2005-12-07 21:14:54 CET): The best there isWhen working with reusable timesheets, I ran into the WEEKNUM() bug. It bothered me a lot. For other reasons as well, I wanted to avoid using ATP-functions. I have been using the WEEKNR function on a daily basis for years now. Alternatives that are available on the web, even from renowned Excel experts, are nowhere near as concise. And it has always proven correct. As I wasn't able to work out my own solution, I am still grateful to have found it, and for you to share it with the world!