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

You calculate week number for Python, con open the same date for Excel, and the numbers no match. You run one SQL query and get another result. You search online and see different one.

No be bug. Na design choice wey every tool dey make differently — and plenty of dem no dey document am well.

At least two big week-numbering system dey wey people dey use, plus different variations inside each one, and no universal default dey. Once you understand the difference, to debug mismatch na seconds work. Until then, e fit dey vex.

The Two Main Systems

ISO 8601 (wey most of Europe, international business, and scientific work dey use)

• Weeks dey run Monday to Sunday
• Week 1 na the week wey get the first Thursday for the year
• One year fit get 52 or 53 weeks
• Week year fit different from calendar year for boundary

US / simple system (wey North America and many spreadsheet default dey use)

• Weeks dey run Sunday to Saturday (or sometimes Monday to Sunday, depending on locale)
• Week 1 na the week wey get January 1
• January 1 always dey inside week 1, no matter the day wey e fall
• Partial weeks for year-end go get high numbers (52 or 53), dem no go move am enter next year

The practical difference go show pass for late December and early January.

December 29–31, 2025 dey inside ISO week 1 of 2026 — because the Thursday for that week (January 1) dey for 2026. US system go still keep dem for week 53 of 2025.

Excel: WEEKNUM vs ISOWEEKNUM

Excel get two different function, wey dey clearer pass most tools.

WEEKNUM(date, [return_type]) — US-style, with option to choose week start

The return_type argument dey control which day go start the week:

return_type = 21 go make WEEKNUM behave like ISO — but dem no document am well and most people no know say e dey.

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

Dem add am for Excel 2013. E go return correct ISO week number and e clear well.

=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: People dey use WEEKNUM with default argument when dem wan compare with ISO-based system. The numbers go match for most dates but e go silently different near year-end.

Google Sheets: Same Functions, Same Caveats

Google Sheets get both WEEKNUM and ISOWEEKNUM with same behaviour like Excel. The default WEEKNUM na US-style, Sunday start. ISOWEEKNUM na ISO 8601.

One difference: Google Sheets dey more consistent to return ISO week year when you use ISOWEEKNUM together with YEAR. If you write =YEAR("2025-12-31") you go get 2025, but the ISO week belong to 2026. No single built-in function dey wey go return ISO week year — you gats calculate am:

=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)))

E long. If you dey do serious week-year work for Sheets, sometimes better make you export go Python or SQL.

Python: isocalendar() Na ISO, But strftime('%W') No Be

Python datetime module give you two different approach.

date.isocalendar() — ISO 8601, e dey return (year, week, weekday)

from datetime import date

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

Notice say the year wey e return na the ISO week year (2026), no be calendar year (2025). Na correct thing and e important — if you need label for week, use iso_year no be d.year.

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

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 combo na ISO-correct. The %W / %U directives na US-style and dem go disagree with ISO for year boundary.

Common mistake: People dey use d.strftime('%W') to get week numbers when other system dem dey use ISO. Dem go agree for 48+ weeks of the year, then dem go quietly different for December and January.

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

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

JavaScript: No Built-In, You Go Write Your Own

JavaScript Date object no get native week number method. Date.getDay() dey return 0 (Sunday) reach 6 (Saturday). You need calculate am yourself or use 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: People dey write simple Math.ceil(dayOfYear / 7) and call am week number. This one no be ISO, no be US-standard — na totally different (and wrong) system.

SQL: E Depend on the Database

Every major database dey handle 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 EXTRACT(week ...) follow ISO 8601. The week year dey available with isoyear.

MySQL / MariaDB — plenty 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 for MySQL dey important and e get 8 options (0–7). Mode 3 na ISO. The default (mode 0) na US-style. Na common source of bug when you dey change database backend.

SQL Server — no be 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 default DATEPART(week, ...) dey use US-style. The isowk alias na the ISO version. If you dey build report wey must match European systems, always use isowk.

SQLite — no native week function

SQLite no get WEEKNUM or EXTRACT(week ...). You fit 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 dey find the Thursday of the week and count from there. E correct but e no obvious.

Quick Cheat Sheet

How You Fit Avoid Mismatch for Real Life

Pick one system and enforce am everywhere. ISO na better default for most new systems — na international standard and na wetin most modern library dey implement.

Always store the ISO week year with the week number. Week 1, 2026 and week 1, 2025 no be the same week. Column wey store only 1 dey ambiguous without year.

Check year-boundary cases directly. Dates like December 28–31 and January 1–3 na where mismatch dey happen. Run your pipeline with test dates inside this range before you deploy.

If you no sure, store and compare full dates. Week numbers na for display and report, no be for primary key or join. If you dey join data across systems with week number, convert go canonical date (the Monday of the week) first.

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