Why Excel, SQL, Python, and JavaScript Give Different Week Numbers for the Same Date

You calculate a week number in Python, then open the same date in Excel, and the numbers don't match. You run a SQL query and get a third result. You search online and find a fourth.

This isn't a bug. It's a design choice that every tool makes differently — and most don't document prominently enough.

There are at least two major week numbering systems in common use, several variations within each, and no universal default. Once you understand the differences, debugging mismatches takes seconds. Until then, they're maddening.

The Two Main Systems

ISO 8601 (used in most of Europe, international business, and scientific contexts)

• Weeks run Monday to Sunday
• Week 1 is the week containing the first Thursday of the year
• A year has 52 or 53 weeks
• The week year can differ from the calendar year at boundaries

US / simple system (used in North America and many spreadsheet defaults)

• Weeks run Sunday to Saturday (or sometimes Monday to Sunday, depending on locale)
• Week 1 is the week containing January 1
• January 1 is always in week 1, regardless of what day it falls on
• Partial weeks at year-end get high numbers (52 or 53), not reassigned to the next year

The practical difference shows up most sharply in late December and early January.

December 29–31, 2025 fall in ISO week 1 of 2026 — because the Thursday of that week (January 1) is in 2026. The US system keeps them in week 53 of 2025.

Excel: WEEKNUM vs ISOWEEKNUM

Excel has two separate functions, which is clearer than most tools.

WEEKNUM(date, [return_type]) — US-style, with configurable week start

The return_type argument controls which day starts the week:

return_type = 21 makes WEEKNUM behave like ISO — but this is poorly documented and most users don't know it exists.

ISOWEEKNUM(date) — ISO 8601, always Monday start, Thursday rule

This was added in Excel 2013. It returns the correct ISO week number and is unambiguous.

=ISOWEEKNUM("2025-12-31")   → 1    (week 1 of 2026)
=WEEKNUM("2025-12-31", 1)   → 53   (week 53 of 2025, Sunday start)
=WEEKNUM("2025-12-31", 2)   → 53   (week 53 of 2025, Monday start)
=WEEKNUM("2025-12-31", 21)  → 1    (ISO-compatible, same as ISOWEEKNUM)

Common mistake: Using WEEKNUM with default arguments when comparing to an ISO-based system. The numbers will match for most dates but silently diverge near year-end.

Google Sheets: Same Functions, Same Caveats

Google Sheets has both WEEKNUM and ISOWEEKNUM with the same behaviour as Excel. The default WEEKNUM is US-style, Sunday start. ISOWEEKNUM is ISO 8601.

One difference: Google Sheets is more consistent about returning the ISO week year when you use ISOWEEKNUM alongside YEAR. If you write =YEAR("2025-12-31") you get 2025, but the ISO week belongs to 2026. There's no single built-in function that returns the ISO week year — you have to calculate it:

=IF(ISOWEEKNUM(A1) > 50, IF(MONTH(A1) = 1, YEAR(A1) - 1, YEAR(A1)),
   IF(ISOWEEKNUM(A1) < 3, IF(MONTH(A1) = 12, YEAR(A1) + 1, YEAR(A1)),
   YEAR(A1)))

This is verbose. If you're doing serious week-year work in Sheets, it's often cleaner to export to Python or SQL.

Python: isocalendar() Is ISO, But strftime('%W') Is Not

Python's datetime module gives you two different approaches.

date.isocalendar() — ISO 8601, returns (year, week, weekday)

from datetime import date

d = date(2025, 12, 31)
d.isocalendar()
# IsoCalendarDate(year=2026, week=1, weekday=3)

Note that the year returned is the ISO week year (2026), not the calendar year (2025). This is correct and important — if you need to label the week, use iso_year not d.year.

strftime('%W') and strftime('%U') — US-style, different week starts

d.strftime('%W')   # Week number, Monday as first day of week → '52'
d.strftime('%U')   # Week number, Sunday as first day of week → '52'
d.strftime('%V')   # ISO week number → '01'
d.strftime('%G')   # ISO week year → '2026'

The %V / %G combination is ISO-correct. The %W / %U directives are US-style and will disagree with ISO at year boundaries.

Common mistake: Using d.strftime('%W') to get week numbers when your other systems use ISO. They'll agree for 48+ weeks of the year, then quietly diverge in December and January.

# Wrong for ISO comparison — year boundary will mismatch
week = int(d.strftime('%W'))

# Correct for ISO
iso_year, iso_week, _ = d.isocalendar()

JavaScript: No Built-In, Roll Your Own

JavaScript's Date object has no native week number method. Date.getDay() returns 0 (Sunday) through 6 (Saturday). You have to calculate it manually or use a library like date-fns or dayjs.

Manual ISO week calculation:

function isoWeek(date) {
  const d = new Date(date)
  d.setHours(0, 0, 0, 0)
  // Thursday in current week decides the year
  d.setDate(d.getDate() + 3 - (d.getDay() + 6) % 7)
  const jan4 = new Date(d.getFullYear(), 0, 4)
  return 1 + Math.round(((d - jan4) / 86400000 - 3 + (jan4.getDay() + 6) % 7) / 7)
}

function isoWeekYear(date) {
  const d = new Date(date)
  d.setDate(d.getDate() + 3 - (d.getDay() + 6) % 7)
  return d.getFullYear()
}

isoWeek(new Date('2025-12-31'))      // 1
isoWeekYear(new Date('2025-12-31'))  // 2026

Using date-fns:

import { getISOWeek, getISOWeekYear } from 'date-fns'

getISOWeek(new Date('2025-12-31'))      // 1
getISOWeekYear(new Date('2025-12-31'))  // 2026

Common mistake: Writing a simple Math.ceil(dayOfYear / 7) calculation and calling it a week number. This gives neither ISO nor US-standard results — it's a completely different (and wrong) system.

SQL: It Depends on the Database

Every major database handles week numbers differently.

PostgreSQL — ISO by default

SELECT EXTRACT(week FROM DATE '2025-12-31');
-- Returns 1 (ISO week)

SELECT DATE_PART('week', DATE '2025-12-31');
-- Returns 1 (same, ISO)

-- Get ISO week year
SELECT EXTRACT(isoyear FROM DATE '2025-12-31');
-- Returns 2026

PostgreSQL's EXTRACT(week ...) follows ISO 8601. The week year is available via isoyear.

MySQL / MariaDB — multiple modes

-- Mode 3 is ISO 8601 (Monday start, week 1 has Thursday)
SELECT WEEK('2025-12-31', 3);   -- 1

-- Mode 0 (default) is US-style, Sunday start
SELECT WEEK('2025-12-31', 0);   -- 53
SELECT WEEK('2025-12-31');      -- 53 (default mode 0)

-- YEARWEEK returns combined year+week
SELECT YEARWEEK('2025-12-31', 3);  -- 202601

The mode argument in MySQL is critical and has 8 options (0–7). Mode 3 is ISO. The default (mode 0) is US-style. This is a common source of bugs when switching database backends.

SQL Server — not ISO by default

-- Default DATEPART(week, ...) is not ISO
SELECT DATEPART(week, '2025-12-31');   -- 53

-- ISO week: use isowk or iso_week
SELECT DATEPART(isowk, '2025-12-31');  -- 1
SELECT DATEPART(iso_week, '2025-12-31');  -- 1 (same)

SQL Server's default DATEPART(week, ...) uses US-style. The isowk alias is the ISO version. If you're building reports that compare to European systems, always use isowk.

SQLite — no native week function

SQLite doesn't have WEEKNUM or EXTRACT(week ...). You use strftime:

-- '%W' is Monday-start, US-style
SELECT strftime('%W', '2025-12-31');   -- 52

-- ISO week requires a workaround
SELECT (strftime('%j', date('2025-12-31', '-3 days', 'weekday 4')) - 1) / 7 + 1;
-- 1

The ISO workaround finds the Thursday of the week and counts from there. It's correct but not obvious.

A Cheat Sheet

How to Avoid Mismatches in Practice

Pick one system and enforce it everywhere. ISO is the better default for most new systems — it's internationally standardised and what most modern libraries implement.

Always store the ISO week year alongside the week number. Week 1, 2026 and week 1, 2025 are different weeks. A column storing just 1 is ambiguous without the year.

Check your year-boundary cases explicitly. The dates December 28–31 and January 1–3 are where mismatches occur. Run your pipeline with test dates in this range before deploying.

When in doubt, store and compare full dates. Week numbers are for display and reporting, not for primary keys or joins. If you're joining data across systems on week number, convert to a canonical date (the Monday of the week) first.

Use the ISO Week Number Calculator to check the correct ISO week number for any date, including the full year calendar view.