

These pages are no longer updated and are only available for archive purposes.Click here to visit the pages with updated information. WeeknumbersThe 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 FunctionThe 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(8WEEKDAY(A1),7)3)),1,1))3+ MOD(WEEKDAY(DATE(YEAR(A1+(MOD(8WEEKDAY(A1),7)3)),1,1))+1,7))/7)+1The 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 userdefined 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 20010314 12:44:38 Printerfriendly version


