Working with Dates and Times in Excel Data

Dates are where Excel and Python disagree most. Excel has no date type at all — it stores a floating-point count of days since an epoch and decides at display time whether that number looks like a date, a time, or a plain number. Python has datetime, which is a real type with real semantics. Every read and every write crosses that boundary, and most date bugs in reporting scripts live exactly there: a column that arrives as 45292.0, a European date silently parsed as American, a timezone-aware timestamp that openpyxl refuses to write. This page covers the model, the parsing, the writing, and the period grouping that reports actually need. It sits within Advanced Data Transformation and Cleaning.

The serial-number model, and the epoch that is off by two days

An Excel date is a number. 1 is 1 January 1900, 45292 is 1 January 2024, and the fractional part is the time of day: .5 is noon, .75 is six in the evening. Whether a cell looks like a date is purely a matter of its number format string — which is why the single most common Excel-from-Python complaint, "my date shows as 45292", is a formatting problem, not a data problem.

The epoch is where it gets strange. Excel treats 1900 as a leap year. It was not: century years are leap years only when divisible by 400. The bug was inherited from Lotus 1-2-3 in the 1980s and deliberately never fixed, because fixing it would shift every date in every existing spreadsheet. So Excel's calendar contains a 29 February 1900 that never existed, and every serial after it is one higher than a correct day count would be.

The practical consequence is a single constant. Anchor conversions at 1899-12-30, not 1900-01-01, and the phantom day cancels out:

import pandas as pd

serials = pd.Series([45292.0, 45658.5, 46023.75])

dates = pd.to_datetime(serials, unit="D", origin="1899-12-30")
print(dates)
# 0   2024-01-01 00:00:00
# 1   2025-01-01 12:00:00
# 2   2025-12-31 18:00:00

Going the other way — turning a Python datetime into the serial Excel expects — is the same arithmetic reversed:

from datetime import datetime

EXCEL_EPOCH = datetime(1899, 12, 30)

def to_excel_serial(dt):
    """Convert a naive datetime to Excel's day-count representation."""
    delta = dt - EXCEL_EPOCH
    return delta.days + delta.seconds / 86_400

print(to_excel_serial(datetime(2024, 1, 1)))        # 45292.0
print(to_excel_serial(datetime(2026, 8, 15, 18)))   # 46249.75

There is a second, rarer epoch: workbooks created on very old Macs use a 1904 date system, anchored at 1904-01-01. Files in the wild carry a flag telling you which. When reading a legacy .xls, xlrd exposes it as book.datemode, as shown in reading .xls files with xlrd and pandas. Dates off by roughly four years and a day are the signature of the wrong epoch.

Reading dates that arrive as text

When a cell carries a date number format, openpyxl and pandas hand you a real datetime and there is nothing to do. The work starts when dates arrive as strings — because somebody typed them, because an upstream CSV lost the typing, or because the export wrote text.

The safe parse names the failure mode explicitly. errors="coerce" turns anything unparseable into NaT rather than raising, which keeps the pipeline running — but only if you then look at what failed:

import pandas as pd

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

raw = df["invoice_date"].copy()
df["invoice_date"] = pd.to_datetime(raw, errors="coerce", format="mixed")

failed = df.loc[df["invoice_date"].isna() & raw.notna(), :]
if len(failed):
    print(f"{len(failed)} unparseable dates:")
    print(raw[failed.index].value_counts().head(10))

Distinguishing a genuinely blank cell from an unparseable one — the raw.notna() clause — is what turns this from a silent data loss into a report. The same principle drives finding and reporting missing values in an Excel file.

Two flags decide the ambiguous cases:

# 03/04/2026: European data means 3 April, not 4 March.
pd.to_datetime(col, dayfirst=True, errors="coerce")

# A single known layout parses far faster than inference,
# and rejects anything that does not match exactly.
pd.to_datetime(col, format="%d/%m/%Y", errors="coerce")

Passing an explicit format is both a performance win and a correctness win: on a column of a million values it is roughly an order of magnitude faster than inference, and it refuses to guess. Reach for format="mixed" only when the column really does contain more than one layout.

Writing dates back so Excel displays them

Writing is where the "shows as 45292" problem appears. openpyxl writes the value correctly and applies a default date format; the moment you set your own number format, or write through a path that does not, you own the display.

from datetime import datetime, date
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"

ws.append(["order", "placed", "due", "processed_at"])
ws.append([1001, date(2026, 8, 15), date(2026, 8, 29),
           datetime(2026, 8, 15, 18, 4, 32)])
ws.append([1002, date(2026, 8, 16), date(2026, 8, 30),
           datetime(2026, 8, 16, 9, 12, 7)])

# Without these, the cells display as raw serial numbers.
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    for cell in row:
        cell.number_format = "yyyy-mm-dd"

for row in ws.iter_rows(min_row=2, min_col=4, max_col=4):
    for cell in row:
        cell.number_format = "yyyy-mm-dd hh:mm:ss"

ws.column_dimensions["B"].width = 14
ws.column_dimensions["C"].width = 14
ws.column_dimensions["D"].width = 22
wb.save("orders.xlsx")

Through pandas the equivalent knobs live on ExcelWriter, which sets a workbook-wide default so you do not touch cells one at a time:

import pandas as pd

df = pd.DataFrame({
    "order": [1001, 1002],
    "placed": 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)

The full vocabulary of Excel format strings — and the difference between mm meaning months and minutes depending on context — is covered in formatting dates in Excel cells with Python.

Timezones: convert, then strip

Excel has no concept of a timezone. A cell holds a day count, and that is all. openpyxl enforces this by raising if you hand it an aware datetime, which is better than the alternative of silently discarding the offset.

from datetime import datetime, timezone
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
aware = datetime(2026, 8, 15, 18, 0, tzinfo=timezone.utc)

ws["A1"] = aware
# ValueError: Excel does not support timezones in datetimes.

The working pattern is three steps: convert everything to one agreed zone, drop the tzinfo, and record the zone somewhere a reader will see.

import pandas as pd

events = pd.DataFrame({
    "event": ["login", "export", "logout"],
    "at": pd.to_datetime([
        "2026-08-15T18:04:00+00:00",
        "2026-08-15T19:30:00+00:00",
        "2026-08-15T21:15:00+00:00",
    ], utc=True),
})

# 1. Convert to the zone the readers think in.
events["at"] = events["at"].dt.tz_convert("Europe/Berlin")

# 2. Drop the offset — Excel cannot hold it.
events["at"] = events["at"].dt.tz_localize(None)

# 3. Say so, in the sheet.
with pd.ExcelWriter("events.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd hh:mm") as writer:
    events.to_excel(writer, sheet_name="Events", index=False, startrow=1)
    writer.sheets["Events"].write(0, 0, "All times Europe/Berlin (CEST)")

Skipping step three is how a report becomes unreproducible six months later, when nobody remembers whether the timestamps were UTC or local. The dedicated walkthrough is handling timezones in Excel timestamps with Python.

Durations, and why they are not dates

A duration in Excel is also a day count — 0.5 is twelve hours — displayed with a bracketed format like [h]:mm. The brackets matter: without them, a 30-hour duration displays as 6 hours, because the format wraps at 24.

import pandas as pd

sessions = pd.DataFrame({
    "user": ["alice", "bob", "carol"],
    "start": pd.to_datetime(["2026-08-15 08:00", "2026-08-15 09:30",
                             "2026-08-14 22:00"]),
    "end": pd.to_datetime(["2026-08-15 17:15", "2026-08-15 12:00",
                           "2026-08-16 06:30"]),
})

sessions["duration"] = sessions["end"] - sessions["start"]

# Excel wants a fraction of a day, not a Timedelta.
sessions["duration_days"] = sessions["duration"] / pd.Timedelta(days=1)

with pd.ExcelWriter("sessions.xlsx", engine="xlsxwriter") as writer:
    out = sessions[["user", "start", "end", "duration_days"]]
    out.to_excel(writer, sheet_name="Sessions", index=False)

    book, sheet = writer.book, writer.sheets["Sessions"]
    # [h] does not wrap at 24 — essential for the 32-hour row.
    elapsed = book.add_format({"num_format": "[h]:mm"})
    sheet.set_column("D:D", 12, elapsed)
    sheet.set_column("B:C", 18,
                     book.add_format({"num_format": "yyyy-mm-dd hh:mm"}))

Writing a Timedelta straight to a cell is the common mistake — it lands as text, and no format string will make Excel sum it.

Grouping by month, quarter and week

Almost every recurring report aggregates by period, and pandas has vectorised tools that make row loops unnecessary.

import pandas as pd

df = pd.read_excel("sales.xlsx")
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df = df.dropna(subset=["date"])

# A readable label for a report column.
df["month"] = df["date"].dt.to_period("M").astype(str)      # "2026-08"
df["quarter"] = df["date"].dt.to_period("Q").astype(str)    # "2026Q3"

# A real timestamp key for aggregation — sorts and joins properly.
monthly = (
    df.groupby(pd.Grouper(key="date", freq="MS"))["amount"]
      .agg(["sum", "count"])
      .rename(columns={"sum": "revenue", "count": "orders"})
)

# Fill months with no sales so a chart shows the gap rather than skipping it.
monthly = monthly.asfreq("MS", fill_value=0)
print(monthly)

The asfreq line is the one people forget. Without it, a month with no rows simply does not appear, and a line chart drawn from that series connects across the gap as though nothing happened. The grouping recipes are expanded in grouping Excel rows by month and quarter with pandas, and the aggregate output feeds naturally into creating pivot tables from Excel data.

Fiscal years need one extra argument. A year ending in March is Q-MAR:

# Fiscal year ending 31 March: April 2026 falls in FY2027 Q1.
df["fiscal_quarter"] = df["date"].dt.to_period("Q-MAR").astype(str)

Date-only, time-only and the cells in between

Excel makes no type distinction between a date, a time, and a timestamp — all three are the same number with a different display format. Python does distinguish them, and the mismatch produces two recurring annoyances.

The first is the phantom midnight. Write a datetime.date and read it back, and you get a datetime at 00:00:00. Nothing was lost; the time component was never there. But an equality test against a date object now fails, and a groupby on the column produces one group per timestamp rather than per day:

import pandas as pd

df = pd.read_excel("orders.xlsx", parse_dates=["invoice_date"])

# Normalise to midnight so date comparisons and grouping behave.
df["invoice_day"] = df["invoice_date"].dt.normalize()

# Or drop to a plain date object when you never need the time again.
df["invoice_date_only"] = df["invoice_date"].dt.date

dt.normalize() keeps the column as datetime64, which is usually what you want — dt.date produces an object column of Python date objects that is slower and loses the .dt accessor. Reach for .dt.date only at the very end, when writing a display column.

The second is the 1899 time. A cell holding only a time of day stores a value below 1 — 0.5 for noon. Read that into pandas and you get a timestamp on 30 December 1899, because the day part is zero:

import pandas as pd

# A "shift start" column that holds times only.
times = pd.Series([0.25, 0.5, 0.75])
as_ts = pd.to_datetime(times, unit="D", origin="1899-12-30")
print(as_ts.dt.time.tolist())
# [datetime.time(6, 0), datetime.time(12, 0), datetime.time(18, 0)]

# Usually more useful: keep them as durations from midnight.
as_td = pd.to_timedelta(times, unit="D")
print(as_td.tolist())
# [Timedelta('0 days 06:00:00'), ..., Timedelta('0 days 18:00:00')]

Treating a time-of-day column as a Timedelta rather than a Timestamp is the cleaner model: you can add it to a date, compare two of them, and sum them, none of which makes sense for a timestamp anchored in 1899.

Date arithmetic that respects the business calendar

Reports rarely want raw calendar arithmetic. "Due in 30 days" usually means 30 business days, and "last month" means the month that ended, not the last 30 days. pandas has offset objects for exactly this, and using them beats hand-rolled loops on both correctness and speed.

import pandas as pd
from pandas.tseries.offsets import BDay, MonthEnd, MonthBegin

df = pd.DataFrame({
    "invoice_date": pd.to_datetime(["2026-08-14", "2026-08-28", "2026-07-31"]),
})

# Business days skip weekends automatically.
df["due"] = df["invoice_date"] + BDay(10)

# Month boundaries, without any day arithmetic.
df["period_start"] = df["invoice_date"] - MonthBegin(1)
df["period_end"] = df["invoice_date"] + MonthEnd(0)

print(df)

MonthEnd(0) is the subtle one: with an offset of zero it means "roll forward to the end of the current month, or stay put if already there", which is what a period-end column wants. MonthEnd(1) would push a date that is already month-end into the following month.

Public holidays need a calendar. pandas ships US federal holidays and lets you define your own, which is what most non-US teams end up doing:

import pandas as pd
from pandas.tseries.holiday import AbstractHolidayCalendar, Holiday
from pandas.tseries.offsets import CustomBusinessDay

class CompanyCalendar(AbstractHolidayCalendar):
    rules = [
        Holiday("New Year", month=1, day=1),
        Holiday("Company Day", month=6, day=12),
        Holiday("Christmas", month=12, day=25),
        Holiday("Boxing Day", month=12, day=26),
    ]

workday = CustomBusinessDay(calendar=CompanyCalendar())

sla = pd.to_datetime(["2026-12-23", "2026-12-24"]) + 2 * workday
print(sla)     # skips both the weekend and the two December holidays

Getting this wrong is a quiet class of reporting bug: a service-level report that counts calendar days will show breaches over every holiday period, and nobody notices until somebody argues about a number. Where the calculation feeds an aggregate that people act on, it belongs in the validated part of your pipeline — see validating Excel data with Python for how to assert on that kind of derived column before a report ships.

One more habit worth building: never compute date arithmetic row by row. A .apply over a date column is typically two orders of magnitude slower than the vectorised offset, and on a workbook with a few hundred thousand rows the difference is the whole runtime of the job:

# Slow — a Python-level call per row.
df["due"] = df["invoice_date"].apply(lambda d: d + pd.Timedelta(days=30))

# Fast — one vectorised operation over the whole column.
df["due"] = df["invoice_date"] + pd.Timedelta(days=30)

Key takeaways

• An Excel date is a number plus a format. A cell showing 45292 holds the right value and the wrong format; set number_format.
• Anchor at 1899-12-30. The 1900-leap-year bug means a naive 1900-01-01 origin is one day off on every date.
• Parse with errors="coerce", then inspect the NaT rows. Separate genuinely blank cells from unparseable ones and report the difference.
• Pass an explicit format when you know it — faster, and it refuses to guess 03/04 for you.
• Excel has no timezones. Convert to one zone, tz_localize(None), and label the sheet.
• Durations are day fractions with a [h]:mm format. The brackets stop the display wrapping at 24 hours.
• Group with Grouper/to_period, and asfreq to fill empty periods so gaps in a series stay visible.

Frequently asked questions

Why does Excel show a number where I wrote a date? The value is correct but the cell has no date number format. Excel stores dates as a day count and decides how to display them from the format string, so set number_format on the cell — for example "yyyy-mm-dd" — and the same value renders as a date.

What is the 1899-12-30 epoch and why not 1900-01-01? Excel reproduces a 1980s Lotus bug that treats 1900 as a leap year, adding a day that never existed. Anchoring conversions at 1899-12-30 cancels that offset, which is why pandas uses it as the origin for Excel serials.

Can I write a timezone-aware datetime to Excel? Not directly — Excel has no timezone concept and openpyxl raises on aware datetimes. Convert to a single agreed zone, drop the tzinfo with tz_localize(None), and record the zone in a header or a separate column.

How do I read a column that mixes 2026-08-15 and 15/08/2026? Parse with pd.to_datetime(col, errors="coerce", format="mixed") and then inspect the NaT rows. For genuinely ambiguous day-first data, pass dayfirst=True so 03/04 is read as the fourth of March rather than the third of April.

Why did my times drift by a second after a round trip? Excel stores the time of day as a binary fraction of a day, so values that are not exact binary fractions pick up a tiny representation error. Round to the second after reading if exact equality matters.

What is the fastest way to group Excel rows into months? Convert the column to datetime once, then use dt.to_period("M") for a label or Grouper(key="date", freq="MS") inside a groupby. Both are vectorised; never loop over rows to build month strings.

Related

• Up to the parent: Advanced Data Transformation and Cleaning — the section this topic belongs to.
• Parse Excel Dates into Python datetimes with pandas — the parsing walkthrough in full.
• Fix Excel Serial Numbers Showing Instead of Dates — the single most common date complaint, solved.
• Handle Timezones in Excel Timestamps with Python — the convert-then-strip pattern in depth.
• Group Excel Rows by Month and Quarter with pandas — period aggregation for recurring reports.
• Cleaning Excel Data with pandas — the sibling topic for the non-date columns.
• Format Dates in Excel Cells with Python — the display side of the same coin.