How to get the week number in Excel, Python, JavaScript, SQL and PHP
Getting a week number in code looks like a one-liner, and in most languages it is — as long as you pick the right function. The wrong one silently returns US-style or “week 0” numbers that agree with ISO 8601 for most of the year and then break in January. This page collects correct snippets for spreadsheets, Python, JavaScript, SQL and PHP, and shows the output for a deliberately awkward date: Friday, January 1, 2027, which is in ISO week 2026-W53.
If you are not sure which numbering system you need, read ISO vs US week numbers first. Unless you have a reason to do otherwise, use ISO 8601 and always store the ISO week-numbering year alongside the week.
Excel
| Goal | Formula | Result for 2027-01-01 |
|---|---|---|
| ISO week | =ISOWEEKNUM(A2) | 53 |
| ISO week (alternative) | =WEEKNUM(A2,21) | 53 |
| US week (Sunday start) | =WEEKNUM(A2) | 1 |
| Monday start, week containing Jan 1 | =WEEKNUM(A2,2) | 1 |
| ISO week-numbering year | =YEAR(A2-WEEKDAY(A2,2)+4) | 2026 |
| ISO label | =YEAR(A2-WEEKDAY(A2,2)+4)&"-W"&TEXT(ISOWEEKNUM(A2),"00") | 2026-W53 |
| Monday of the week in A2 | =A2-WEEKDAY(A2,2)+1 | 2026-12-28 |
The ISO-year formula uses the same trick as the standard itself: WEEKDAY(A2,2) numbers Monday as 1 and Sunday as 7, so A2-WEEKDAY(A2,2)+4 is the Thursday of the same week, and the year of that Thursday is the ISO year. ISOWEEKNUM is available in Excel 2013 and later; older versions support WEEKNUM(A2,21) from Excel 2010.
To get the Monday that starts ISO week W of year Y (in cells B1 and B2), use =DATE(B1,1,4)-WEEKDAY(DATE(B1,1,4),2)+1+(B2-1)*7. It starts from January 4, which is always in week 1.
Google Sheets
Google Sheets supports the same functions with the same arguments: =ISOWEEKNUM(A2) for ISO weeks and =WEEKNUM(A2, type) with types 1, 2, 11–17 and 21. The ISO-year and Monday formulas above work unchanged. To fill a whole column at once, wrap the formula in ARRAYFORMULA, for example =ARRAYFORMULA(IF(A2:A="",,ISOWEEKNUM(A2:A))).
Python
The standard library has everything you need. date.isocalendar() returns the ISO year, week and weekday, and strftime has dedicated ISO codes.
from datetime import date
d = date(2027, 1, 1)
iso_year, iso_week, iso_weekday = d.isocalendar()
print(iso_year, iso_week, iso_weekday) # 2026 53 5
print(d.strftime("%G-W%V-%u")) # 2026-W53-5
# Monday of a given ISO week (Python 3.8+)
print(date.fromisocalendar(2026, 53, 1)) # 2026-12-28
# Number of ISO weeks in a year: December 28 is always in the last week
print(date(2026, 12, 28).isocalendar()[1]) # 53
Be careful with format codes: %G is the ISO year and %V the ISO week, while %Y is the calendar year. %U (Sunday start) and %W (Monday start) are “week 0” codes — both return 00 for January 1, 2027. Mixing %Y with %V produces the wrong label 2027-W53.
In pandas, use the vectorised accessor:
import pandas as pd
s = pd.to_datetime(pd.Series(["2026-12-31", "2027-01-01", "2027-01-04"]))
print(s.dt.isocalendar()) # columns: year, week, day
This prints week 53 for December 31, 2026 and January 1, 2027, and week 1 (of 2027) for January 4, 2027. The older Series.dt.week property was removed in pandas 2.0.
JavaScript
JavaScript’s built-in Date has no week-number method (the newer Temporal API adds weekOfYear and yearOfWeek, but it is not yet available in every browser). The function below implements the ISO rule directly. It works in UTC internally so that daylight-saving changes and the user’s time zone cannot move the date.
// ISO 8601 week number and week-numbering year for a calendar date.
// Pass year, month (1-12) and day so time zones cannot shift the date.
function isoWeek(year, month, day) {
const d = new Date(Date.UTC(year, month - 1, day));
const weekday = d.getUTCDay() || 7; // Monday = 1 … Sunday = 7
d.setUTCDate(d.getUTCDate() + 4 - weekday); // move to the Thursday of this week
const weekYear = d.getUTCFullYear();
const jan1 = Date.UTC(weekYear, 0, 1);
const week = Math.ceil(((d - jan1) / 86400000 + 1) / 7);
return { weekYear, week };
}
// Today's week in the user's own time zone:
const now = new Date();
const { weekYear, week } = isoWeek(now.getFullYear(), now.getMonth() + 1, now.getDate());
console.log(`${weekYear}-W${String(week).padStart(2, '0')}`);
If you need the US convention instead (Sunday start, week 1 contains January 1):
// US week number: weeks start on Sunday, week 1 contains January 1.
function usWeek(year, month, day) {
const d = Date.UTC(year, month - 1, day);
const jan1 = new Date(Date.UTC(year, 0, 1));
const dayOfYear = (d - jan1) / 86400000; // 0-based
return Math.floor((dayOfYear + jan1.getUTCDay()) / 7) + 1;
}
We tested both functions against this site’s own calculation library, which is in turn tested against Python’s ISO calendar, for every day from January 1, 1970 to December 31, 2199 (84,006 days) with no differences. A common bug in snippets found online is to construct new Date('2027-01-01'): that string is parsed as UTC midnight, which is still December 31 in the Americas if you then read local fields.
SQL
Each database has its own functions, and their defaults differ.
MySQL and MariaDB
-- MySQL / MariaDB
SELECT WEEK('2027-01-01', 3); -- 53 (ISO week, mode 3)
SELECT WEEKOFYEAR('2027-01-01'); -- 53 (same as WEEK(date, 3))
SELECT YEARWEEK('2027-01-01', 3); -- 202653 (ISO year + week)
SELECT DATE_FORMAT('2027-01-01', '%x-W%v'); -- '2026-W53'
SELECT WEEK('2027-01-01'); -- 0 (default mode 0: Sunday start, week 0)
WEEK() takes a mode from 0 to 7. Only modes 3 (weeks 1–53) and 1 (weeks 0–53) use the ISO “four days” rule with Monday start, and only mode 3 matches ISO for the first days of January. If you group by week, group by YEARWEEK(date, 3) rather than YEAR(date), WEEK(date, 3), so that the days around New Year land in the right week-year.
PostgreSQL
-- PostgreSQL
SELECT EXTRACT(WEEK FROM DATE '2027-01-01'); -- 53 (ISO week)
SELECT EXTRACT(ISOYEAR FROM DATE '2027-01-01'); -- 2026
SELECT to_char(DATE '2027-01-01', 'IYYY-"W"IW-ID'); -- '2026-W53-5'
PostgreSQL’s WEEK field is always ISO. Pair it with ISOYEAR, not YEAR. In to_char, IYYY, IW and ID are the ISO year, week and weekday; WW is a different “day-of-year divided by seven” count and is rarely what you want. date_trunc('week', d) returns the Monday that starts the ISO week.
SQL Server
-- SQL Server
SELECT DATEPART(ISO_WEEK, '2027-01-01'); -- 53 (ISO week)
SELECT DATEPART(WEEK, '2027-01-01'); -- 1 (week containing Jan 1; first day set by DATEFIRST)
DATEPART(WEEK, …) depends on the session’s SET DATEFIRST setting (7, Sunday, for US English), and week 1 is always the week containing January 1. Use ISO_WEEK for ISO numbers, available since SQL Server 2008.
SQLite
-- SQLite 3.46.0 and later
SELECT strftime('%G-W%V', '2027-01-01'); -- '2026-W53'
SELECT strftime('%W', '2027-01-01'); -- '00' (Monday-based, week 0 before first Monday)
Older SQLite versions only have %W (Monday start, week 0) and %U-style counting; the ISO codes %G, %V and %u were added in version 3.46.0 (2024).
PHP
<?php
$d = new DateTimeImmutable('2027-01-01');
echo $d->format('o-\WW-N'); // 2026-W53-5 (o = ISO year, W = ISO week, N = ISO weekday)
echo (int) $d->format('W'); // 53
// Monday of ISO week 1 of 2027
$monday = (new DateTimeImmutable())->setISODate(2027, 1, 1);
echo $monday->format('Y-m-d'); // 2027-01-04
// Weeks in a year: December 28 is always in the last ISO week
echo (new DateTimeImmutable('2026-12-28'))->format('W'); // 53
In PHP date formats, W is the ISO week (with a leading zero), o is the ISO week-numbering year and N the ISO weekday. The equivalent of the Python %Y mistake is writing Y-\WW instead of o-\WW. setISODate() converts an ISO year, week and weekday back to a date.
Checklist for week-number code
- Decide on ISO or another system explicitly, and name it in your documentation.
- Store or display the ISO week-numbering year with the week; never the calendar year.
- Allow for week 53. 2026 has one; so do 2032 and 2037 (full list).
- Convert timestamps to a local date in the relevant time zone before computing the week.
- Test with dates such as 2024-12-30 (2025-W01), 2026-12-31 (2026-W53), 2027-01-01 (2026-W53), 2027-01-04 (2027-W01) and 2028-01-01 (2027-W52).
How these snippets were checked
The Python, pandas, JavaScript and SQLite snippets on this page were run and their outputs compared with the comments. The Excel and MySQL behaviour is taken from Microsoft’s and MySQL’s reference documentation. The PostgreSQL, SQL Server and PHP snippets follow their official documentation but were not executed for this page; if you find a discrepancy, please let us know.