ISO Week Number in Excel — ISOWEEKNUM Guide
Updated: May 2026
Excel has two week number functions and they produce different results. WEEKNUM uses Sunday as the first day by default. ISOWEEKNUM follows ISO 8601 with Monday start and the first-Thursday rule. Mixing them silently corrupts reports near year boundaries.
Free · No upload · 100% in-browser
The right function: ISOWEEKNUM
Available since Excel 2013, ISOWEEKNUM(date) returns the ISO 8601 week number for any date. The function takes a single argument — the date — and returns a number between 1 and 53.
If A1 contains January 2, 2026, this returns 1. If A1 contains December 31, 2026, this returns 53. There is no second argument — the function always follows ISO 8601.
ISOWEEKNUM is available in Excel 2013+, Excel 365, Google Sheets, LibreOffice Calc, and Numbers. In older Excel versions (2010 and earlier), you need a manual formula.
Why not WEEKNUM?
WEEKNUM(date, [return_type]) is older and offers multiple modes. The default (return_type = 1) counts weeks starting on Sunday, which is not ISO 8601. Using WEEKNUM(date, 2) uses Monday start, but still uses a different week-1 definition: it assigns week 1 to the week containing January 1, regardless of what day it falls on.
The result: for dates in early January or late December, WEEKNUM(date, 2) and ISOWEEKNUM(date) can differ by exactly one week. This one-off is a frequent source of errors in year-end reporting and cross-system data imports.
Getting the ISO year (not just the week number)
The ISO year and the calendar year are not always the same. YEAR(A1) returns the calendar year, which is wrong for late December or early January dates that cross the ISO year boundary. To get the correct ISO year, use:
This formula finds the nearest Thursday of the same week (adding up to 3 days forward or back), then takes the calendar year of that Thursday — which is always the correct ISO year.
For a complete ISO week label in the format 2026-W22:
Filtering and grouping by ISO week in pivot tables
When building pivot tables with week-level aggregation, add a helper column next to your date column:
- Column B:
=ISOWEEKNUM(A2)— the week number. - Column C:
=YEAR(A2+3-MOD(WEEKDAY(A2,2)-1,7))— the ISO year. - Column D (optional combined key): the formula above that produces
2026-W22.
Group the pivot table by column D or by (ISO year, ISO week) pair. This gives correct grouping even for rows near year boundaries, where grouping by calendar year and WEEKNUM would misplace a few rows.
Manual formula for Excel 2010 and earlier
If ISOWEEKNUM is not available, this legacy formula computes the ISO week number for a date in A1:
It replicates the ISO 8601 algorithm: find the Thursday of the week, compute the year that Thursday belongs to, locate the start of week 1 of that year, and divide the day difference by 7.