Fix Excel Serial Numbers Showing Instead of Dates

You open the workbook your script produced and the date column reads 45292, 45293, 45294. Nothing is broken — those are the right values. Excel stores every date as a count of days and decides at display time whether to render that number as a date, and your cells are missing the instruction that makes it do so. This guide fixes it three ways: at write time with openpyxl, at write time through pandas, and as a repair pass over a workbook you already have. It builds on the storage model explained in Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

Before fixing anything, confirm which of two situations you are in, because they need different fixes:

import pandas as pd

df = pd.read_excel("report.xlsx")
print(df["invoice_date"].dtype)
print(df["invoice_date"].head(3).tolist())

If the dtype is datetime64[ns], the data is fine and only the display is wrong — go to Step 1. If the dtype is int64 or float64 and the values look like five-digit numbers, the column is genuinely numeric and needs converting first — go to Step 4.

Step 1 — Set the number format with openpyxl

number_format is a per-cell attribute holding an Excel format string. Setting it is the entire fix:

from datetime import date
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Sheet1"]

# Column B holds dates; skip the header row.
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = "yyyy-mm-dd"

wb.save("report_fixed.xlsx")

Two refinements make this robust in a real script. First, find the column by header name rather than hard-coding B, so an inserted column does not silently misformat a different field. Second, widen the column — a date in a narrow column displays as #####, which people report as a different bug:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

DATE_FORMATS = {
    "invoice_date": "yyyy-mm-dd",
    "due_date": "yyyy-mm-dd",
    "processed_at": "yyyy-mm-dd hh:mm",
}

def format_date_columns(path, dest, sheet=None, widths=True):
    """Apply date number formats to columns identified by their header."""
    wb = load_workbook(path)
    ws = wb[sheet] if sheet else wb.active

    headers = {
        str(c.value).strip(): c.column
        for c in ws[1] if c.value is not None
    }

    applied = []
    for name, fmt in DATE_FORMATS.items():
        col = headers.get(name)
        if col is None:
            continue
        for (cell,) in ws.iter_rows(min_row=2, min_col=col, max_col=col):
            cell.number_format = fmt
        if widths:
            # Leave room, or Excel renders ##### instead of the date.
            ws.column_dimensions[get_column_letter(col)].width = len(fmt) + 4
        applied.append(name)

    wb.save(dest)
    return applied

print("formatted:", format_date_columns("report.xlsx", "report_fixed.xlsx"))

The format strings you will reach for most:

Note that mm means months after a date part and minutes after an hour part — hh:mm is minutes, yyyy-mm is months. Getting that wrong produces a cell showing the month number where you wanted minutes, which is a genuinely confusing bug to read.

Step 2 — Format at write time through pandas

If your script writes the workbook, fix it there and skip the repair pass entirely. ExcelWriter takes workbook-wide defaults:

import pandas as pd

df = pd.DataFrame({
    "order": [1001, 1002],
    "invoice_date": pd.to_datetime(["2026-08-15", "2026-08-16"]),
    "processed_at": pd.to_datetime(["2026-08-15 18:04", "2026-08-16 09:12"]),
})

with pd.ExcelWriter(
    "orders.xlsx",
    engine="xlsxwriter",
    date_format="yyyy-mm-dd",
    datetime_format="yyyy-mm-dd hh:mm",
) as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)

date_format applies to date-only values and datetime_format to values carrying a time. Both work with the openpyxl engine as well.

For per-column control — different formats on different columns, plus widths — drop to the xlsxwriter objects the writer exposes:

import pandas as pd

with pd.ExcelWriter("orders.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)

    book = writer.book
    sheet = writer.sheets["Orders"]

    day = book.add_format({"num_format": "yyyy-mm-dd"})
    stamp = book.add_format({"num_format": "yyyy-mm-dd hh:mm"})

    # set_column formats a whole column in one call — no per-cell loop.
    sheet.set_column("B:B", 13, day)
    sheet.set_column("C:C", 19, stamp)

One trap with pandas: to_excel replaces the target sheet rather than merging into it. Formatting applied to a sheet before a later to_excel call is discarded. Format after writing, never before.

Step 3 — Repair a workbook you did not write

Sometimes the file arrives already broken and you do not control the producer. A repair pass can find the date columns itself, by testing whether the values sit in a plausible date range:

The third gate matters. Without it, a column of order quantities that happens to sit between 20,000 and 60,000 gets reformatted as dates — a repair that makes the file worse:

import re
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

DATE_NAME = re.compile(r"(date|day|_at$|_on$|timestamp)", re.I)
PLAUSIBLE = range(20_000, 60_001)      # roughly 1954 to 2064

def repair_date_formats(path, dest, fmt="yyyy-mm-dd", sample=200):
    """Find numeric columns that are really dates and give them a date format."""
    wb = load_workbook(path)
    fixed = []

    for ws in wb.worksheets:
        headers = {c.column: str(c.value or "").strip() for c in ws[1]}

        for col, name in headers.items():
            if not DATE_NAME.search(name):
                continue                         # gate 3

            values, numeric = 0, 0
            for (cell,) in ws.iter_rows(min_row=2, max_row=1 + sample,
                                        min_col=col, max_col=col):
                if cell.value is None:
                    continue
                values += 1
                if isinstance(cell.value, (int, float)) \
                        and int(cell.value) in PLAUSIBLE:
                    numeric += 1                 # gates 1 and 2

            # Require a clear majority, so one stray number cannot trigger it.
            if values and numeric / values > 0.9:
                for (cell,) in ws.iter_rows(min_row=2, min_col=col, max_col=col):
                    cell.number_format = fmt
                ws.column_dimensions[get_column_letter(col)].width = len(fmt) + 4
                fixed.append(f"{ws.title}!{name}")

    wb.save(dest)
    return fixed

print("repaired:", repair_date_formats("broken.xlsx", "repaired.xlsx"))

Returning the list of what it changed — rather than silently rewriting the file — is what makes this safe to run unattended. Log it, and a surprising result is visible instead of buried.

Step 4 — When the values really are numbers

If pandas reports int64 or float64, a number format alone is only half a fix: Excel will display dates, but the column stays numeric to every Python reader. Convert the values, then write and format:

import pandas as pd

df = pd.read_excel("report.xlsx")

# Guard against nonsense before converting.
plausible = df["invoice_date"].between(20_000, 60_000)
if not plausible.all():
    print(f"{(~plausible).sum()} values outside the plausible serial range")

df.loc[plausible, "invoice_date"] = pd.to_datetime(
    df.loc[plausible, "invoice_date"], unit="D", origin="1899-12-30"
)
df["invoice_date"] = pd.to_datetime(df["invoice_date"], errors="coerce")

with pd.ExcelWriter("report_fixed.xlsx", engine="xlsxwriter",
                    date_format="yyyy-mm-dd") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)

The 1899-12-30 origin is not arbitrary — it cancels Excel's 1900 leap-year bug, as explained in the parent topic. Anchoring at 1900-01-01 leaves every date one day late.

Common pitfalls and fixes

Performance and scale notes

Per-cell formatting is the slow path. Each cell.number_format = ... assignment in openpyxl creates or looks up a style entry, and on a column of half a million rows that adds real time and memory.

Two faster routes. With xlsxwriter, set_column applies one format to an entire column in a single call, independent of row count:

import pandas as pd

big = pd.DataFrame({
    "id": range(500_000),
    "invoice_date": pd.date_range("2024-01-01", periods=500_000, freq="min"),
})

with pd.ExcelWriter("big.xlsx", engine="xlsxwriter") as writer:
    big.to_excel(writer, sheet_name="Data", index=False)
    fmt = writer.book.add_format({"num_format": "yyyy-mm-dd hh:mm"})
    writer.sheets["Data"].set_column("B:B", 19, fmt)

With openpyxl, define the style once and reuse the object rather than assigning a fresh string per cell — Excel caps a workbook at roughly 64,000 distinct cell formats, and a loop that creates a new one each iteration will eventually hit it:

from openpyxl.styles import NamedStyle
from openpyxl import load_workbook

date_style = NamedStyle(name="iso_date", number_format="yyyy-mm-dd")

wb = load_workbook("report.xlsx")
wb.add_named_style(date_style)
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.style = "iso_date"      # one shared style, not one per cell
wb.save("report_fixed.xlsx")

For genuinely large workbooks, the cheapest fix of all is not to create the problem: write with the correct date_format in the first place, using the streaming approach in writing large DataFrames with write-only mode, and no repair pass is ever needed.

Conclusion

A cell showing 45292 is a display problem, not a data problem. Set number_format on the cell with openpyxl, or date_format and datetime_format on a pandas ExcelWriter, and the same value renders as a date. Widen the column so it does not turn into #####. If the column is genuinely numeric rather than a formatted date, convert it with the 1899-12-30 origin before writing. And for repair passes over files you did not create, require a date-like header name as well as a plausible value range, so a column of quantities never gets reformatted as dates.

Frequently asked questions

Is my data wrong when Excel shows 45292? No. The value is correct — 45292 is Excel's internal representation of 1 January 2024. Only the cell's display format is missing, so setting number_format on the cell fixes it without touching the data.

Which number format string should I use?"yyyy-mm-dd" for an unambiguous date, "yyyy-mm-dd hh:mm" when the time matters, and "[h]:mm" for durations. Avoid locale-dependent forms like "mm/dd/yyyy" in reports that cross borders.

Why did my format disappear after pandas wrote the file?to_excel replaces the sheet rather than merging into it, so formatting applied beforehand is lost. Set date_format and datetime_format on the ExcelWriter instead, or apply the formats after writing.

Do I have to format every cell individually? No. Set the format once per column by iterating that column's cells with openpyxl, or use xlsxwriter's set_column, which applies one format to a whole column in a single call.

What if the values really are numbers rather than dates? Convert them first with pd.to_datetime(col, unit="D", origin="1899-12-30"), then write and format. A number format alone will make Excel display a plausible date, but the column will still be numeric to pandas.

Related

• Up to the parent: Working with Dates and Times in Excel Data — the serial model behind this whole problem.
• Parse Excel Dates into Python datetimes with pandas — the reading side of the same boundary.
• Format Dates in Excel Cells with Python — the wider vocabulary of Excel format strings.
• Set Column Width and Row Height in openpyxl — fixing the ##### half of the problem.
• Format Excel Cells as Currency with Python — the same mechanism applied to money.