2001-03-14 Dates 0 86
Update: In Excel version 2013 the built in functions have finally been updated with a function for returning the ISO weeknumber: ISOWEEKNUM(date).
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 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 FunctionThe 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)+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 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 Function ISOYearWeek(ByVal dtmDate As Date) As String ' returns the correct ISO Year and Week number: yyyy-mm ' the Thursday in the same week as dtmDate will contain the correct ISO year and week ISOYearWeek = vbNullString If dtmDate < 1 Then Exit Function dtmDate = dtmDate + 4 - Weekday(dtmDate, vbMonday) ' thursday in the same week as dtmDate ISOYearWeek = Year(dtmDate) & "-" & Format(DatePart("ww", dtmDate, vbMonday, vbFirstFourDays), "00") End FunctionThe correct ISO year and week can also be calculated with a worksheet formula like this:
=YEAR(A1-WEEKDAY(A1;2)+4)&"-"&TEXT(WEEKNUM(A1;21);"00") ' thursday in a week contains the correct ISO year
Replace A1 with a cell reference to a cell containing a date value.