Convert Text Months and Weekdays to Dates

Spreadsheets kept by hand rarely store periods as dates. A column headed Month holds Jan 2026, February 2026, Sept-26; another sheet has Q3 2026, Week 14 or FY27 P03; a rota lists Mon, Tue, Wed. They read well, but they sort alphabetically — April before January — they cannot be filtered by date range, and they cannot be joined to a table keyed by real dates. Converting them is a matter of telling pandas exactly what each format means, plus handling the cases its parser does not know, such as Sept or a fiscal year that starts in April. This page converts each common kind of label into a real date or a pandas period, with one rule per format, and reports every label that matched none of them instead of turning it silently into a missing value. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

The examples work on small Series of labels, the way they arrive from a column read with read_excel. Everything uses English month and day names; the last section shows how to handle other languages.

Step 1 — Month and year, in several spellings

import pandas as pd

months = pd.Series(["Jan 2026", "February 2026", "Mar-26", "Sept-26", "2026-10", "Nov 26", "Dec. 2026", "Smarch 2026"])

FIX = {r"\bSept\b": "Sep", r"\bJune\b": "Jun", r"\bJuly\b": "Jul", r"\.": ""}
FORMATS = ["%b %Y", "%B %Y", "%b-%y", "%Y-%m", "%b %y"]

def parse_month(s):
    clean = s.str.strip().replace(FIX, regex=True)
    out = pd.Series(pd.NaT, index=s.index, dtype="datetime64[ns]")
    for fmt in FORMATS:
        todo = out.isna()
        out[todo] = pd.to_datetime(clean[todo], format=fmt, errors="coerce")
    return out.dt.to_period("M")

result = pd.DataFrame({"label": months, "month": parse_month(months)})
print(result.to_string(index=False))
print("not parsed:", result.loc[result["month"].isna(), "label"].tolist())

The approach is to try a short list of explicit formats in turn, each only on the labels not yet parsed. An explicit format is far safer than letting pandas guess: %b-%y means a three-letter month, a hyphen and a two-digit year, and nothing else will match it by accident. Before parsing, a small set of replacements normalises what the formats cannot express — the four-letter Sept that people often write but %b does not recognise, full names like June in a column of abbreviations, and stray full stops. The result is converted to a monthly Period, which is the right type for a label that means a whole month rather than a particular day: it sorts correctly, supports arithmetic like next month, and turns into the first or last day of the month when a date is needed. Smarch 2026 matches nothing and is listed rather than hidden — the kind of typo that would otherwise drop a month of data from a report without anyone noticing.

Step 2 — Quarters, ISO weeks and fiscal periods

quarters = pd.Series(["Q1 2026", "2026 Q2", "Q3-26", "2026Q4"])
q = quarters.str.extract(r"Q([1-4])\D*(\d{2,4})|(\d{4})\s*Q([1-4])")
year = q[1].fillna(q[2]).astype(int).map(lambda y: y + 2000 if y < 100 else y)
qtr = q[0].fillna(q[3]).astype(int)
quarter_periods = pd.PeriodIndex.from_fields(year=year, quarter=qtr, freq="Q")
print(dict(zip(quarters, quarter_periods.astype(str))))

weeks = pd.Series(["2026-W01", "2026-W14", "2026 week 53"])
w = weeks.str.extract(r"(\d{4})\D*(\d{1,2})$").astype(int)
monday = pd.to_datetime(w[0].astype(str) + "-W" + w[1].astype(str).str.zfill(2) + "-1", format="%G-W%V-%u", errors="coerce")
print(dict(zip(weeks, monday.dt.date)))

fiscal = pd.Series(["FY27 P01", "FY27 P03", "FY27 P10", "FY27 P12"])
fy = fiscal.str.extract(r"FY(\d{2})\s*P(\d{2})").astype(int)
start_month = 4                                         # the fiscal year starts in April and is named after the year it ends
calendar = (pd.PeriodIndex.from_fields(year=2000 + fy[0] - 1, month=start_month, freq="M") + (fy[1] - 1)).astype(str)
print(dict(zip(fiscal, calendar)))

Quarter labels come in several orders, so a regular expression with two alternatives captures the number and the year whichever comes first, and two-digit years are widened to four. PeriodIndex.from_fields then builds proper quarterly periods. Week numbers need care: Week 1 of a year is defined differently by different conventions, and the ISO 8601 definition — weeks start on Monday, and week 1 is the one containing the year's first Thursday — is the one most European businesses and most systems use. The directives %G (ISO year), %V (ISO week) and %u (weekday, Monday is 1) parse it exactly, giving the Monday that starts each week. Two results show why the convention matters. ISO week 1 of 2026 starts on Monday 29 December 2025, so a label for the first week of 2026 belongs partly to the previous calendar year. And 2026 is one of the years with 53 ISO weeks — it begins on a Thursday — so 2026 week 53 is valid and starts on 28 December; in a year with only 52 weeks the same label would fail to parse and be reported. Fiscal periods need one fact that is not in the label — the month the fiscal year starts — after which each period is that month plus an offset. Here FY27 runs from April 2026, so period 1 is April 2026 and period 10 is January 2027.

Step 3 — Weekday names in the right order

days = pd.Series(["Mon", "Wednesday", "fri", "Sun", "Tues", "Thur"])
ORDER = ["mon", "tue", "wed", "thu", "fri", "sat", "sun"]
day_number = days.str.strip().str.lower().str[:3].map({d: i for i, d in enumerate(ORDER)})
weekday = pd.Categorical(days.str.strip().str[:3].str.title(), categories=[d.title() for d in ORDER], ordered=True)
print(pd.DataFrame({"label": days, "number": day_number, "weekday": weekday}).sort_values("weekday").to_string(index=False))

week_start = pd.Timestamp("2026-10-05")                 # a Monday
print((week_start + pd.to_timedelta(day_number, unit="D")).dt.strftime("%a %d %b").tolist())

A weekday on its own is not a date, but it has a fixed order and often needs turning into a date within a given week. The first three letters identify every English weekday, which absorbs Tues, Thur and Wednesday alike, and a small lookup turns them into numbers from 0 for Monday — the same convention as pandas' dayofweek. An ordered categorical makes the names sort Monday to Sunday instead of alphabetically, which matters for every rota or opening-hours table. Adding the day number to the Monday of a week gives real dates: for the week starting Monday 5 October 2026, Wednesday becomes 7 October and Sun becomes 11 October. The same lookup is a natural place to reject anything that is not a weekday at all, such as Holiday typed into a rota column.

Other languages

Month names in other languages — März, janvier, giugno — are best handled with an explicit mapping to month numbers rather than by changing the system locale, which affects the whole process and is not available on every machine. A dictionary of the twelve names, and their usual abbreviations, for each language in the data is short, reviewable and reliable; normalise the labels to lower case and strip accents before looking them up, and the same reporting of unmatched labels applies. The same is true of weekday names.

Store the result, keep the label

Once converted, the period should replace the label as the column the report works with — sorting, grouping, joining to a calendar table — but it is worth keeping the original label alongside for a while. When a total looks wrong, the first question is often which rows went into which month?, and having the label next to the parsed period answers it immediately. When the workbook is written back out, a monthly period is best stored as the first day of the month formatted as mmm yyyy, so Excel sees a real date and readers see Sep 2026; a quarter can be stored the same way with a custom label in a neighbouring column.

Common pitfalls

• Letting pandas guess the format. Give explicit formats and try them in order.
• Assuming %b knows Sept. Normalise common abbreviations before parsing.
• Storing a month as a day. Use a monthly Period for labels that mean a whole month.
• Non-ISO week numbers. Use %G-W%V-%u for ISO weeks, and check which convention the source uses.
• Silent NaT. Report every label that no rule matched.

Conclusion

To convert period labels into dates, normalise the text, try a short list of explicit formats in order and turn months into monthly periods; extract quarters with a regular expression and build them with PeriodIndex.from_fields; parse ISO weeks with %G-W%V-%u; map fiscal periods using the fiscal year's start month; and turn weekday names into numbers and an ordered categorical. Whatever matches no rule should be listed, not quietly dropped.

Frequently asked questions

How do I convert "Jan 2026" to a date in pandas? Use pd.to_datetime(s, format="%b %Y"), and .dt.to_period("M") if the value means the whole month.

Why does pandas not parse "Sept"? The %b directive expects three-letter abbreviations; replace "Sept" with "Sep" before parsing.

How do I convert an ISO week number to a date? Build a string like 2026-W14-1 and parse it with format="%G-W%V-%u", which gives the Monday of that week.

How do I sort weekday names Monday to Sunday? Make them an ordered pd.Categorical with the categories in weekday order.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in Excel data.
• Fix Mixed Day-First and Month-First Dates in Excel Data — ambiguous full dates.
• Group Excel Rows by Month and Quarter with pandas — using the periods.
• Calculate the Nth Weekday of a Month — weekdays as dates.
• Parse Excel Dates into Python datetimes with pandas — full dates rather than labels.