Week numbers in Excel and Google Sheets

The two functions

=WEEKNUM(A1)        ' US system: Sunday start, week 1 contains Jan 1
=ISOWEEKNUM(A1)     ' ISO 8601: Monday start, week 1 contains Jan 4

If you work with anyone outside the US — or with this site's charts — you almost always want ISOWEEKNUM (Excel 2013+, all versions of Google Sheets). The two functions agree in many years and then silently differ by one in others, which is how "our week numbers don't match" tickets are born. Today, ISOWEEKNUM(TODAY()) returns 29 and default WEEKNUM(TODAY()) returns 29 (live values).

WEEKNUM return types

WEEKNUM(date, type) takes a second argument that sets the week's first day:

=WEEKNUM(A1,21) is identical to =ISOWEEKNUM(A1) and works in Excel 2010, which predates the dedicated function. Every type except 21 uses the "contains January 1" rule and can return 53 or even 54 as a label for the final partial week.

The ISO week-year formula

The trap: ISOWEEKNUM("2027-01-01") returns 53 — correct, but it is week 53 of 2026. If you build a code by gluing YEAR() to ISOWEEKNUM(), every date near New Year comes out wrong (2027-W53 does not exist). Use the ISO week-year instead:

=YEAR(A1-WEEKDAY(A1,2)+4)

It shifts the date to the Thursday of its week (subtract the Monday-based weekday, add 4), whose calendar year is the ISO week-year — the same trick every ISO implementation uses. Works identically in Google Sheets.

Building week codes

' ISO code, e.g. 2026-W29
=YEAR(A1-WEEKDAY(A1,2)+4) & "-W" & TEXT(ISOWEEKNUM(A1),"00")

' YYWW date code, e.g. 2629
=TEXT(MOD(YEAR(A1-WEEKDAY(A1,2)+4),100),"00") & TEXT(ISOWEEKNUM(A1),"00")

' Monday of the week in A1 (to label rows with real dates)
=A1-WEEKDAY(A1,2)+1

All three respect the week-year rule, so December 29–31 and January 1–3 come out right. What the codes mean and where to use them: week number formats.

Grouping and comparing data by week

Three field-tested tips:

• Group by code, not by number. Add a helper column with the full ISO code (2026-W29) and pivot on that — grouping on the bare week number merges week 12 of different years into one bucket.
• Prefer the week's Monday as the axis. =A1-WEEKDAY(A1,2)+1 gives each row its week-start date; charts then space weeks correctly across year boundaries and sort with no effort.
• Mind week 53 in year-over-year comparisons. A 53-week year (like 2026) has one week with no prior-year counterpart; retail practice is to compare against the nearest matching week rather than dropping it. Background: how many weeks in a year.

Elsewhere in the Microsoft world the same 1–21 type codes appear in Power Query (Date.WeekOfYear), DAX (WEEKNUM), and VBA (DatePart("ww", …)); SQL Server uses DATEPART(ISO_WEEK, date).