Date & Time
ISO week number and YYYY-Www label formula (year-boundary safe)
Calculate exact ISO-8601 week numbers (1–53) and ISO week-years ("2026-W53") using Thursday-anchor date math that works in every spreadsheet.
Why YEAR(d) & "-W" & ISOWEEKNUM(d) breaks on Jan 1–3!
Under ISO-8601, weeks run Monday–Sunday and Week 1 is the week containing the year's first THURSDAY. Consequently, Friday January 1, 2027 belongs to ISO week 53 of 2026 ("2026-W53")—so concatenating YEAR(d) produces a false "2027-W53"!
Finding that week's Thursday first (th = d - WEEKDAY(d, 2) + 4) solves BOTH problems at once: YEAR(th) is the true ISO week-year, and INT((th - DATE(YEAR(th), 1, 1)) / 7) + 1 is the exact ISO week number 1..53 without needing ISOWEEKNUM!
| Calendar Date (A) | Same-Week Thursday (th) | ISO Week # | True ISO YYYY-Www |
|---|---|---|---|
| 2026-01-01 (Thu) | 2026-01-01 | 1 | 2026-W01 |
| 2026-07-04 (Sat) | 2026-07-02 | 27 | 2026-W27 |
| 2027-01-01 (Fri) | 2026-12-31 | 53 | 2026-W53 (NOT 2027!) |
Exact ISO-8601 YYYY-Www string and integer week formulas
Zero-pad single-digit weeks with RIGHT("0" & wk, 2) so alphabetical sorting matches chronological order ("2026-W02" before "2026-W10").
=LET(th, INT(A2) - WEEKDAY(INT(A2), 2) + 4, wk, INT((th - DATE(YEAR(th), 1, 1)) / 7) + 1, YEAR(th) & "-W" & RIGHT("0" & wk, 2))Numeric ISO week number (1–53) only
The Thursday-based expression returns the ISO week number for the supported numeric dates in this example.
=LET(th, INT(A2) - WEEKDAY(INT(A2), 2) + 4, INT((th - DATE(YEAR(th), 1, 1)) / 7) + 1)Inputs and interpretation
Use supported numeric Gregorian dates on a consistent time basis. The formulas use the Thursday of the week to determine the ISO week-year, which can differ from the calendar year. Dates around the spreadsheet 1900 leap-year compatibility anomaly are outside this example.
Try the example
Use the original values or change them to test your understanding.