Excel Guides

ISO Week Numbers in Excel

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.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.