Validate Dates Fall Within an Expected Period

A monthly import — September's expenses, timesheets or sales — should contain September dates. In practice a few rows always do not: a receipt dated 2062 because someone typed the year backwards, 03/09/2026 entered by a colleague who writes month first, a date that arrived as the number 46280 because a column was formatted as text, a line from August that should have been in last month's file, a date in October because the export ran on the 2nd. Loaded as they are, those rows land in the wrong period or vanish from date-filtered reports. Validating the dates before loading catches them, and most of them can not only be flagged but explained. This page builds a date validator in pandas that tags each problem row with a reason — out of period, future, implausible year, probable day/month swap, Excel serial number, not a date, non-working day — and suggests a corrected date where the evidence supports one. It belongs to Validating Excel Data with Python.

Prerequisites

pip install pandas openpyxl holidays

The sample is twelve expense lines for September 2026 with the date column read as text, the way a column with mixed content arrives from Excel. The checker is run on 5 October 2026.

Step 1 — Parse what can be parsed, keep the rest visible

import pandas as pd

raw = pd.read_excel("expenses_sep.xlsx", dtype={"Date": str})
period = pd.Period("2026-09", freq="M")
today = pd.Timestamp("2026-10-05")

iso = pd.to_datetime(raw["Date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(raw["Date"], format="%d/%m/%Y", errors="coerce")
serial = pd.to_numeric(raw["Date"], errors="coerce")
from_serial = pd.to_datetime(serial, unit="D", origin="1899-12-30").where(serial.between(20_000, 80_000))
raw["Parsed"] = iso.fillna(dmy).fillna(from_serial)
raw["How"] = (iso.notna().map({True: "ISO", False: ""}) + dmy.notna().map({True: "d/m/Y", False: ""})
              + from_serial.notna().map({True: "Excel serial", False: ""})).replace("", "not a date")
print(raw[["Line", "Date", "Parsed", "How"]].to_string(index=False))

The date column is read as text so that nothing is converted silently on the way in. Each expected format is then tried explicitly: ISO dates, day-first dates with slashes, and plain numbers in the range Excel uses for dates — around 20,000 to 80,000 covers 1954 to 2119 — converted from Excel's serial numbering, which counts days from 30 December 1899. Recording how each value was parsed is useful later: a value that only parsed as an Excel serial is a sign the source column was formatted wrongly, and a value that did not parse at all — next week — needs a person. Nothing is dropped at this stage; the unparsed row stays in the table with an empty date. In this file nine values are ISO dates, one is day-first, one — 46280 — only makes sense as an Excel serial for 15 September, and one is not a date at all.

Step 2 — Tag each problem with a reason

import holidays

bank = holidays.country_holidays("GB", subdiv="ENG", years=[2026])
start, end = period.start_time, period.end_time

def reasons(row):
    d, out = row["Parsed"], []
    if pd.isna(d):
        return ["not a date"]
    if d.year < 2000 or d.year > today.year + 1:
        out.append("implausible year")
    elif d > today:
        out.append("in the future")
    if not (start <= d <= end) and "implausible year" not in out:
        out.append("outside the period")
    if d.weekday() >= 5 or d.date() in bank:
        out.append("non-working day")
    if row["How"] == "Excel serial":
        out.append("arrived as a number")
    return out

raw["Problems"] = raw.apply(reasons, axis=1)
flagged = raw[raw["Problems"].str.len() > 0]
print(flagged[["Line", "Date", "Parsed", "Problems"]].to_string(index=False))

Each rule describes one kind of problem, and a row can have several. A year before 2000 or more than a year ahead is implausible — almost always a typing slip rather than a real expense from 1926. Any other date after today is in the future. Dates outside September are outside the period. Weekends and public holidays are flagged as non-working days, which is not an error for every kind of data but is worth a look in an expense or timesheet import. And a value that only made sense as an Excel serial number is flagged even when its date is fine, because the file it came from needs fixing. Returning a list of reasons per row, rather than a single yes or no, is what makes the output useful to the person who has to fix it.

Step 3 — Suggest corrections where the evidence is clear

def suggestion(row):
    d = row["Parsed"]
    if pd.isna(d):
        return None
    if "implausible year" in row["Problems"]:
        for year in (period.year, period.year - 1):
            try:
                alt = d.replace(year=year)
            except ValueError:
                continue
            if start <= alt <= end:
                return alt.date()
    if row["How"] == "d/m/Y" and d.day <= 12:
        swapped = d.replace(day=d.month, month=d.day)
        if start <= swapped <= end and not (start <= d <= end):
            return swapped.date()
    return None

raw["Suggested"] = raw.apply(suggestion, axis=1)
out = raw.loc[raw["Problems"].str.len() > 0, ["Line", "Date", "Amount", "Problems", "Suggested"]]
out["Problems"] = out["Problems"].str.join("; ")
print(out.to_string(index=False))

Some problems have an obvious fix. A date whose year is implausible but whose day and month fall in the reporting month almost certainly has a mistyped year — 2062-09-21 becomes 21 September 2026, and 1926-09-14 becomes 14 September 2026. A date that parsed day-first and fell outside the period, but would fall inside it with day and month swapped, was probably entered month-first. In this file the day-first 03/09/2026 is 3 September, inside the period, so it is left alone — swapping it would be guessing. Suggestions are only offered when they land inside the reporting period; everything else is flagged for a person to decide, and no suggestion is applied automatically.

Errors and warnings

Not every flag means the same thing. A date that is not a date, or that falls outside the period, is an error: the row cannot be loaded into September as it stands. A Saturday expense or a value that arrived as a serial number is a warning: the date may well be right — people do buy train tickets at weekends — but someone should glance at it. Treating every flag as an error makes the validator so strict that people stop reading its output; treating none as errors lets bad rows through. A simple severity per reason, used to decide which rows are held back and which are loaded with a note, keeps the balance.

With that split, the Saturday expense on line 8 and the serial-number date on line 7 would be loaded with a note, and only five lines would wait for a correction.

Step 4 — Load the clean rows, return the rest

clean = raw[raw["Problems"].str.len() == 0]
with pd.ExcelWriter("expenses_sep_validation.xlsx") as xw:
    out.to_excel(xw, sheet_name="Needs attention", index=False)
    clean[["Line", "Parsed", "Amount"]].rename(columns={"Parsed": "Date"}).to_excel(xw, sheet_name="Ready to load", index=False)
print(f"{len(clean)} rows ready, {len(out)} need attention, value held back: {out['Amount'].sum():,.2f}")

The validator splits the file into rows ready to load and rows that need attention, and writes both to a workbook the submitter can work through. Showing the value held back alongside the count matters: seven lines worth 694.80, as here, is a conversation with the submitter; seven lines worth a hundred thousand is an escalation. Rows with a suggested date still go to the attention list, because the decision to accept a correction belongs to the person who knows the expense. For dates that may be day-first or month-first throughout a whole file, the approach in Fix Mixed Day-First and Month-First Dates in Excel Data works out the convention from the data as a whole.

Common pitfalls

• Letting pandas parse on read. Read dates as text and parse with explicit formats.
• Dropping unparseable values. Keep them, tagged not a date.
• One pass/fail flag. Give each row the list of reasons.
• Applying suggested fixes automatically. Suggest only; let a person accept them.
• Ignoring Excel serial numbers. Convert them, and flag the source as needing a fix.

Conclusion

To validate dates against a reporting period, read the column as text, parse each value with explicit formats including Excel serial numbers, and tag every row with the reasons it fails — not a date, implausible year, in the future, outside the period, non-working day, arrived as a number. Suggest a corrected date only where it lands inside the period — a mistyped year, or a day and month swap — and split the file into rows ready to load and rows for a person to fix.

Frequently asked questions

How do I find dates outside a month in pandas? Compare the parsed dates with period.start_time and period.end_time of a monthly Period.

How do I convert an Excel serial number to a date in pandas? Use pd.to_datetime(value, unit="D", origin="1899-12-30").

How can I detect day and month swapped in dates? For day-first dates with a day of 12 or less, check whether swapping day and month moves the date into the expected period.

Should invalid dates be corrected automatically? No; suggest corrections where the evidence is clear and let the person responsible accept them.

Related

• Up one level: Validating Excel Data with Python — checking data quality.
• Fix Mixed Day-First and Month-First Dates in Excel Data — the whole-file view.
• Flag Public Holidays in Excel Data with Python — the holiday calendar.
• Build a Data Quality Scorecard for an Excel File — dates as one of many checks.