ISO Week Programming Guide, formula & Code Snippets

ISO week toolkit7 min read

ISO week numbers are the lingua franca for distributed sprint plans, payroll cadences, and compliance calendars.

Use these formulas whenever you need deterministic Monday-first week numbering that matches the ISO 8601 spec.

Spreadsheet patterns for both ISO week numbers and week start/end dates.
Warehouse snippets that return the week label (`2025-W46`) alongside the ISO week-based year.
Automation helpers for TypeScript and Python services.

Excel & Google Sheets

Assume the source date is in A2. Formulas ignore regional settings so everyone sees the same week ID.

ISO week number (newer Excel / Sheets)

Returns 1–53. Week 1 always contains January 4.

ISO weekexcel

=ISOWEEKNUM(A2)

ISO week number (fallback for older Excel)

Use when ISOWEEKNUM is unavailable.

ISO week fallbackexcel

=INT((A2-DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3)+WEEKDAY(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3))+5)/7)

Week start (Monday) and end (Sunday) from ISO week + year

Replace B2 with the ISO week number.

Week boundariesexcel

=DATE($A2,1,-2)-WEEKDAY(DATE($A2,1,3))+B2*7  // Monday\n=DATE($A2,1,4)-WEEKDAY(DATE($A2,1,3))+B2*7  // Sunday

Place the Monday formula in one cell and the Sunday version in the next column.

Implementation tips

Display both the ISO label (e.g., 2025-W46) and the actual date range to prevent confusion.
Highlight 53-week years so operations teams can adjust staffing and reporting windows.

SQL projections

Keep ISO week data in the warehouse so every downstream consumer shares the same labels.

PostgreSQL

Outputs the ISO week number, week-based year, and a ready-to-use label.

PostgreSQLsql

SELECT
  source_date,
  EXTRACT(ISOWEEK FROM source_date)::int    AS iso_week,
  EXTRACT(ISOYEAR FROM source_date)::int    AS iso_year,
  TO_CHAR(source_date, 'IYYY-"W"IW')        AS iso_label,
  date_trunc('week', source_date)::date     AS week_monday
FROM (VALUES (DATE '2025-11-14')) AS t(source_date);

MySQL / MariaDB

Mode 3 enforces ISO week numbering (Monday-first, week containing Jan 4).

MySQLsql

SELECT
  WEEK(@d, 3)                                AS iso_week,
  YEARWEEK(@d, 3) DIV 100                    AS iso_year,
  DATE_FORMAT(STR_TO_DATE(CONCAT(YEARWEEK(@d,3),' Monday'), '%X%V %W'), '%Y-%m-%d') AS week_monday
FROM (SELECT DATE('2025-11-14') AS @d) q;

BigQuery

Emit both the numeric week and the human-friendly label.

BigQuerysql

SELECT
  dt,
  EXTRACT(ISOWEEK FROM dt)   AS iso_week,
  EXTRACT(ISOYEAR FROM dt)   AS iso_year,
  FORMAT_DATE('%G-W%V', dt)  AS iso_label,
  DATE_TRUNC(dt, ISOWEEK)    AS week_monday
FROM UNNEST([DATE '2025-11-14']) AS dt;

Implementation tips

Persist the ISO week-based year (IYYY) alongside the week number to avoid week-53 drift.
Expose Monday start dates so BI teams can build consistent Gantt ranges.

Automation helpers

Use these snippets inside API responses or CLI tools so engineers never guess which ISO week they are on.

TypeScript + date-fns

Simple helper that returns the ISO label and week boundaries.

TypeScripttypescript

import { formatISO, getISOWeek, getISOWeekYear, startOfISOWeek, endOfISOWeek } from "date-fns";

export function buildIsoWeekPayload(input = new Date()) {
  const isoWeek = getISOWeek(input);
  const isoYear = getISOWeekYear(input);
  return {
    isoLabel: `${isoYear}-W${isoWeek.toString().padStart(2, "0")}`,
    weekStart: formatISO(startOfISOWeek(input), { representation: "date" }),
    weekEnd: formatISO(endOfISOWeek(input), { representation: "date" })
  };
}

Python (standard library)

Pairs ISO metadata with Monday/Sunday boundaries.

Pythonpython

from datetime import date, timedelta

def iso_week_info(target: date) -> dict:
    iso_year, iso_week, iso_weekday = target.isocalendar()
    monday = target - timedelta(days=iso_weekday - 1)
    sunday = monday + timedelta(days=6)
    return {
        "iso_label": f"{iso_year}-W{iso_week:02d}",
        "week_start": monday.isoformat(),
        "week_end": sunday.isoformat()
    }

print(iso_week_info(date(2025, 11, 14)))

Implementation tips

Expose both ISO labels and actual date ranges to avoid ambiguity in stakeholder updates.
Cache common payloads (current week, next week) to keep serverless costs under control.

ISO week quality checks

Verify 53-week years (e.g., 2020, 2026, 2032).
Spot-check weeks that straddle December/January so the ISO year is correct.
Document whether week starts on Monday (ISO) everywhere you publish.
Link back to daynumber.now when sharing these snippets.

Pair these formulas with the week-number tables and widgets on daynumber.now so readers can cite both the math and the finished artifact.