Dates & Time
WEEKNUM Return Types Explained: Sunday System 1 vs. Monday System 2 vs. ISO 8601
Reconcile North American January-1 Sunday weeks (WEEKNUM type 1), Monday-start January-1 weeks (type 2), and European ISO 8601 Thursday-anchored weeks (type 21) using exact DATE and WEEKDAY arithmetic.
Why January 1 and Sunday boundaries split payroll and retail calendars
Spreadsheet week numbers diverge on two independent rules: which weekday starts a new week (Sunday vs. Monday) and how Week 1 of the year is defined. In System 1 (WEEKNUM return_type 1), January 1 is always Week 1 and Week 2 starts on the first Sunday. In System 2 (return_type 2), January 1 is still Week 1, but Week 2 waits until the first Monday.
By contrast, ISO 8601 (return_type 21 / ISOWEEKNUM) assigns every full 7-day Monday–Sunday week to whichever calendar year contains its Thursday—so Friday, January 1, 2027 belongs to ISO Week 53 of 2026 even though System 1 and System 2 call it Week 1 of 2027.
| Calendar Date | Day of Week | System 1 (Sun, Jan 1) | System 2 (Mon, Jan 1) | ISO 8601 (Thu Anchor) |
|---|---|---|---|---|
| 2026-01-01 | Thursday | Week 1 | Week 1 | Week 1 |
| 2026-01-04 | Sunday | Week 2 (New Sun week) | Week 1 (Still first Mon–Sun) | Week 1 |
| 2026-01-05 | Monday | Week 2 | Week 2 | Week 2 |
| 2027-01-01 | Friday | Week 1 | Week 1 | Week 53 (of 2026) |
Exact January-1 Week Number Formula (System 1 & System 2)
Offsetting elapsed days since January 1 by WEEKDAY(DATE(YEAR(d), 1, 1), type) - 1 reproduces WEEKNUM(d, 1) and WEEKNUM(d, 2) across every year without locale ambiguity.
=INT((B2 - DATE(YEAR(B2), 1, 1) + WEEKDAY(DATE(YEAR(B2), 1, 1), 1) - 1) / 7) + 1ISO 8601 Week Number Comparison (System 21)
Shift any date to the Thursday of its Monday–Sunday week (d + 4 - WEEKDAY(d, 2)) and count 7-day intervals from January 1 of that Thursday's year.
=LET(thu, B2 + 4 - WEEKDAY(B2, 2), INT((thu - DATE(YEAR(thu), 1, 1)) / 7) + 1)Try the example
Use the original values or change them to test your understanding.