Excel does not have a built-in function to display the ISO week number, but you can create a user-defined function (UDF) to do this. The following code, which you can paste into a module sheet in your workbook, implements the ISOWeekNum function.
Function ISOWeekNum(DateIn As Date) As Integer ' Returns the ISO 8601 week number for a given date. ' (ISO 8601 weeks start on Monday and end on Sunday.) ' Requires the DateIn argument to be a valid date. Dim Jan1 As Date Dim Dec31 As Date Dim WeekNum As Integer Jan1 = DateSerial(Year(DateIn), 1, 1) Dec31 = DateSerial(Year(DateIn), 12, 31) WeekNum = Int((DateDiff("d", Jan1, DateIn) + 6) / 7) If WeekNum = 0 Then WeekNum = ISOWeekNum(DateSerial(Year(DateIn) - 1, 12, 31)) ElseIf WeekNum = 53 And Dec31.DayOfWeek <> vbSunday Then WeekNum = 1 End If ISOWeekNum = WeekNum End Function
After you enter this code, you can use the ISOWeekNum function just like any other Excel function. For example, if cell A1 contains a date value, you can display the ISO week number for that date in cell B1 with the formula
=ISOWeekNum(A1). If cell A1 is blank or contains an invalid date, this formula will return an error value.