Convert Year-Week Codes to Dates with pandas

Planning tools, ERP systems and production schedules love week numbers. A capacity plan exported to Excel has columns headed 2026-W14, a forecast file has a "Week" column containing 202614, and a supplier's delivery schedule says "wk 14/2026". To join these to daily sales or to show them on a calendar, each code must become a date — usually the Monday that starts the week. The catch is that "week 14" is not one thing. The ISO standard starts weeks on Monday and assigns each week to the year that contains its Thursday, so 29 December can belong to week 1 of the next year; American conventions start weeks on Sunday and count from the week containing 1 January. Some years have 53 ISO weeks, most have 52. This page normalises the different code formats, converts them with ISO rules in pandas, handles week 53 and Sunday-based weeks, checks against Excel's week functions, and converts dates back into week codes. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

plan.xlsx holds a production plan with a week code and planned units per row. The codes arrive in six different styles, as they do when several people and systems feed one sheet, and two of them are suspicious: 2025-W53 (2025 has no week 53) and 2026-W53 (which does exist). LibreOffice (soffice) recalculates Excel's ISOWEEKNUM and WEEKNUM in Step 4 for comparison.

Step 1 — Normalise the codes to one format

Before converting, reduce every style to YYYY-Www. A regular expression that finds a four-digit year and a one- or two-digit week, in either order, handles all the common styles:

import re
import pandas as pd

plan = pd.read_excel("plan.xlsx", dtype={"Week": str})

def normalise_week(code):
    s = str(code).upper().replace("WK", "W")
    m = re.search(r"(\d{4})\D*W?(\d{1,2})\b", s) or re.search(r"W?(\d{1,2})\D+(\d{4})", s)
    if not m:
        return None
    a, b = m.groups()
    year, week = (a, b) if len(a) == 4 else (b, a)
    if len(s.strip()) == 6 and s.isdigit():               # 202615 → year 2026, week 15
        year, week = s[:4], s[4:]
    return f"{int(year)}-W{int(week):02d}"

plan["ISO code"] = plan["Week"].map(normalise_week)
print(plan[["Week", "ISO code"]].to_string(index=False))

2026-W14, 202615, wk 16/2026, 2026W17, W2 2027 and 2026 - 18 all become YYYY-Www. Reading the column with dtype=str matters: a code like 202615 would otherwise arrive as a number, and one like 2026-W01 could be mangled by date guessing. Codes the pattern cannot interpret come back as None and should be reported rather than guessed. This normalised form is the ISO 8601 week notation, which is also what Excel users see in many planning add-ins.

Step 2 — ISO week codes to dates

Python's %G (ISO year), %V (ISO week) and %u (ISO weekday, Monday = 1) parse ISO weeks correctly. Add a weekday to the code and parse:

plan["Week start"] = pd.to_datetime(plan["ISO code"] + "-1", format="%G-W%V-%u", errors="coerce")
plan["Week end"] = plan["Week start"] + pd.Timedelta(days=6)
print(plan[["ISO code", "Week start", "Week end"]].to_string(index=False))

2026-W14 starts on Monday 30 March 2026 and ends on Sunday 5 April. 2025-W01 starts on Monday 30 December 2024 — ISO week 1 is the week containing the first Thursday of the year (equivalently, the week containing 4 January), so it can begin in the previous December. That is the most common surprise with week codes, and the reason the format uses %G (ISO year) rather than %Y (calendar year): with %Y the result would be wrong around every new year. errors="coerce" turns codes that cannot exist into NaT instead of raising, which the next step uses.

Step 3 — Week 53, and catching codes that cannot exist

A year has 53 ISO weeks when 1 January falls on a Thursday, or on a Wednesday in a leap year; otherwise 52. Python can tell you how many weeks any year has — 28 December is always in the last week:

from datetime import date

def iso_weeks_in(year):
    return date(year, 12, 28).isocalendar().week

print({y: iso_weeks_in(y) for y in range(2024, 2030)})
plan["Year"] = plan["ISO code"].str[:4].astype(int)
plan["Weeknum"] = plan["ISO code"].str[-2:].astype(int)
plan["Valid"] = plan["Weeknum"].between(1, 52) | (plan["Weeknum"] == plan["Year"].map(iso_weeks_in))
print(plan.loc[~plan["Valid"], ["Week", "ISO code"]].to_string(index=False))
print("2026-W53 starts:", plan.loc[plan["ISO code"] == "2026-W53", "Week start"].iloc[0].date())

Of the years shown, only 2026 has 53 ISO weeks — 1 January 2026 is a Thursday — so 2026-W53 is a real week starting Monday 28 December 2026, while 2025-W53 cannot exist. Here the parser rejected 2025-W53 and errors="coerce" turned it into NaT, but an explicit check like this one is still worth having: it states the rule, catches the problem before any join, and produces a list someone can act on rather than a silent blank. Report invalid codes back to whoever produced the plan; the likely fix is a typo.

Step 4 — Check against Excel, and Sunday-based weeks

Excel has two week functions: ISOWEEKNUM, which follows ISO 8601, and WEEKNUM, which by default starts weeks on Sunday and counts week 1 as the week containing 1 January. Compare both with Python for dates around a new year:

import subprocess
from openpyxl import Workbook, load_workbook

test_dates = pd.to_datetime(["2024-12-29", "2024-12-30", "2025-01-01", "2025-01-05", "2025-01-06", "2026-12-31", "2027-01-03"])
wb = Workbook()
ws = wb.active
for i, d in enumerate(test_dates, start=1):
    ws.cell(i, 1, d.to_pydatetime())
    ws.cell(i, 2, f"=_xlfn.ISOWEEKNUM(A{i})")
    ws.cell(i, 3, f"=WEEKNUM(A{i})")
wb.save("weeks.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "weeks.xlsx"], check=True, capture_output=True)
c = load_workbook("calc/weeks.xlsx", data_only=True).active

for i, d in enumerate(test_dates, start=1):
    iso = d.isocalendar()
    us_week = int(d.strftime("%U")) + (0 if date(d.year, 1, 1).weekday() == 6 else 1)
    print(f"{d:%a %d %b %Y}  ISO {iso.year}-W{iso.week:02d} (ISOWEEKNUM {c.cell(i, 2).value})  "
          f"US-style week {us_week} (LibreOffice WEEKNUM {c.cell(i, 3).value})")

Python's isocalendar() agrees with ISOWEEKNUM on every date, including 29 December 2024 (ISO week 52 of 2024) and 30 December 2024 (week 1 of 2025). ISOWEEKNUM is another function stored with the _xlfn. prefix; without it the spreadsheet shows #NAME?. The US-style column follows Excel's documented WEEKNUM rule — weeks start on Sunday and week 1 is the week containing 1 January, numbered within the date's own calendar year — computed with Python's %U plus one unless the year starts on a Sunday, so 30 December 2024 is week 53 and 3 January 2027 week 2. LibreOffice's WEEKNUM, however, returned week 1 for the last days of December 2024 and 2026 in this test, numbering them as part of the next year's first week. Spreadsheet programs evidently do not agree on Sunday-based week numbers at the year boundary, which is one more reason to prefer ISO weeks, and to ask which program and convention produced a file's week numbers before converting them. If it came from a European ERP or planning system it is almost always ISO.

Step 5 — From dates back to week codes, and joining

Going the other way — labelling daily data with week codes so it can be joined to the plan — uses isocalendar() on a date column:

daily = pd.DataFrame({"Date": pd.date_range("2026-03-30", "2026-05-10", freq="D")})
iso = daily["Date"].dt.isocalendar()
daily["ISO code"] = iso["year"].astype(str) + "-W" + iso["week"].astype(str).str.zfill(2)
daily["Units made"] = 180
actual = daily.groupby("ISO code")["Units made"].sum()
joined = plan.set_index("ISO code")[["Week start", "Planned units"]].join(actual, how="inner")
print(joined.to_string())
plan.drop(columns=["Year", "Weeknum"]).to_excel("plan_with_dates.xlsx", index=False)

Series.dt.isocalendar() returns ISO year, week and day for every date; combined into the same YYYY-Www form, the codes join directly to the plan. Using the ISO year from isocalendar — not Date.dt.year — is what keeps 30 December 2024 in week 2025-W01 on both sides of the join. The plan with real week start and end dates is written back to Excel, ready for a calendar view or a chart with a proper date axis. Fiscal weeks that follow a company calendar instead of ISO are covered in Build a 4-4-5 Fiscal Calendar with pandas.

Common pitfalls

• %Y instead of %G for ISO weeks. Dates around new year land in the wrong year.
• Week codes read as numbers. Read the column as text so codes like 202601 survive.
• Trusting the parser with week 53. Check validity explicitly; some Python versions roll over silently.
• Mixing ISO and US week numbers. They differ around every new year; confirm the convention.
• Calendar year in the join key. Use the ISO year from isocalendar() when building codes from dates.

Conclusion

To convert week codes to dates, first normalise every style to YYYY-Www, then parse with pd.to_datetime(code + "-1", format="%G-W%V-%u") to get each ISO week's Monday, adding six days for its Sunday. Remember that ISO week 1 can start in the previous December and that only some years have a week 53 — validate codes rather than trusting the parser — and check whether a file uses ISO weeks or US-style Sunday weeks, which Excel's ISOWEEKNUM and WEEKNUM distinguish. To join daily data to week-based plans, build codes from dates with isocalendar(), using the ISO year.

Frequently asked questions

How do I convert 2026-W14 to a date in pandas? Append a weekday and parse with ISO directives: pd.to_datetime("2026-W14" + "-1", format="%G-W%V-%u") returns Monday 30 March 2026.

Why does week 1 of 2025 start in December 2024? ISO week 1 is the week containing the first Thursday of the year. In 2025 that is 2 January, so the week starts on Monday 30 December 2024.

Which years have 53 ISO weeks? Years in which 1 January falls on a Thursday, or on a Wednesday in a leap year. date(year, 12, 28).isocalendar().week gives the number of weeks in any year.

What is the difference between Excel's WEEKNUM and ISOWEEKNUM? ISOWEEKNUM follows ISO 8601 with Monday-start weeks. WEEKNUM by default starts weeks on Sunday and counts the week containing 1 January as week 1; spreadsheet programs differ in how they number the last days of December.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in Excel data.
• Group Excel Data by Fiscal Year and ISO Week — grouping by week.
• Convert Text Dates like 20240131 with pandas — other compact date codes.
• Build a Date Table for Excel Reports with pandas — weeks alongside months and quarters.