Format Dates in Excel Cells with Python

Format dates in Excel cells with openpyxl: write real datetime objects so Excel stores a date serial, then apply yyyy-mm-dd, mmm d yyyy, or dd/mm/yyyy display codes.

Excel stores a date as a number — a serial count of days from a base date — and displays it through a format. If you write a string like "2024-03-09", Excel keeps text and cannot reformat it, sort it as a date, or do date math on it. The fix is to write a real Python date/datetime so Excel stores a true serial, then set the display code. This guide, part of Applying Number and Date Formats in Excel, is fully runnable.

Prerequisites and install

Bash

pip install openpyxl

For the pandas section: pip install pandas openpyxl. The standard-library datetime module is all you need for the core examples. openpyxl does not require Excel.

Step 1: Write a real date so Excel stores a serial

Assign a datetime.date or datetime.datetime object directly. openpyxl converts it to Excel's serial number and applies a default date format, which you then override with your own code.

Python

from datetime import date, datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Event", "When"])
ws.append(["Invoice issued", date(2024, 3, 9)])
ws.append(["Logged at", datetime(2024, 3, 9, 14, 30)])

ws["B2"].number_format = "yyyy-mm-dd"        # 2024-03-09
ws["B3"].number_format = "yyyy-mm-dd hh:mm"  # 2024-03-09 14:30

wb.save("dates_basic.xlsx")
print("B2 is a real date:", ws["B2"].value)  # datetime.date(2024, 3, 9)

ws["B2"].value round-trips as a date object, proving Excel holds a serial, not text.

Step 2: Choose a display layout

The same stored date can render many ways. Pick a code and assign it. m/d are unpadded, mm/dd are zero-padded, mmm is the short month name, and yyyy is the four-digit year.

Python

from datetime import date
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
d = date(2024, 3, 9)
for row, code in enumerate(["yyyy-mm-dd", "mmm d, yyyy", "dd/mm/yyyy", "d mmmm yyyy"], start=1):
    ws.cell(row=row, column=1, value=d).number_format = code

wb.save("dates_layouts.xlsx")
print("Same date serial, four display codes")

This writes 2024-03-09, Mar 9, 2024, 09/03/2024, and 9 March 2024 — all from the identical stored value.

Step 3: Format a date column across rows

Loop the date column and set one code on each data cell, skipping the header. Because every cell holds a real date, the format simply changes presentation.

Python

from datetime import date
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Invoice", "Due"])
for n, d in [("INV-001", date(2024, 1, 31)),
             ("INV-002", date(2024, 2, 29)),
             ("INV-003", date(2024, 3, 15))]:
    ws.append([n, d])

for cell in ws["B"][1:]:               # column B, skip header
    cell.number_format = "mmm d, yyyy"

wb.save("dates_column.xlsx")
print("Formatted", ws.max_row - 1, "due dates")

Why a string date cannot be reformatted

If a cell holds the text "2024-03-09", applying a date number_format does nothing — there is no serial to reformat. Excel treats text as text. Detect and fix this by parsing the string back into a date before writing.

Python

from datetime import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "2024-03-09"                          # text — a date format would be ignored
ws["A2"] = datetime.strptime("2024-03-09", "%Y-%m-%d").date()  # real date
ws["A2"].number_format = "mmm d, yyyy"

print("A1 type:", type(ws["A1"].value).__name__)  # str
print("A2 type:", type(ws["A2"].value).__name__)  # date
wb.save("dates_text_vs_real.xlsx")

A1 stays a str; A2 is a real date that now displays as Mar 9, 2024. Always parse incoming string dates with datetime.strptime (or pandas.to_datetime) before writing.

Handling pandas datetime columns on export

pandas writes proper datetime cells when the column dtype is datetime64, so the values arrive as real serials. But to_excel applies a default format; reopen with openpyxl to set your own. If your column is still strings, convert it with pd.to_datetime first.

Python

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({
    "Invoice": ["INV-001", "INV-002"],
    "Due": ["2024-01-31", "2024-02-29"],     # strings on purpose
})
df["Due"] = pd.to_datetime(df["Due"])         # convert to real datetimes
df.to_excel("pandas_dates.xlsx", index=False, sheet_name="Invoices")

wb = load_workbook("pandas_dates.xlsx")
ws = wb["Invoices"]
for cell in ws["B"][1:]:                       # Due is column B
    cell.number_format = "dd/mm/yyyy"

wb.save("pandas_dates.xlsx")
print("Converted, exported, and reformatted the Due column")

Exporting with index=False keeps the date column at B; see Write a Pandas DataFrame to Excel Without the Index.

Common pitfalls

Symptom	Cause	Fix
Date format ignored	Cell holds a string, not a date serial	Parse with `datetime.strptime`/`pd.to_datetime` before writing
Cannot sort or filter as dates	Values are text	Convert to real `date`/`datetime` objects
Time component shows when you want a date	Wrote a `datetime`, not a `date`	Use `dt.date()`, or a date-only code like `yyyy-mm-dd`
`ValueError` writing a tz-aware datetime	Excel has no timezone concept	Strip tzinfo: `dt.replace(tzinfo=None)` (after converting to your target zone)
Off-by-one near early-1900 dates	Excel's 1900 leap-year quirk	Avoid pre-1900 dates; modern dates are unaffected

A note on timezones and the 1900 quirk

Excel dates are timezone-naive: there is no UTC offset stored. Convert a timezone-aware datetime to your intended zone first, then strip the tzinfo with dt.replace(tzinfo=None) before writing, or openpyxl raises a ValueError. Separately, Excel intentionally treats 1900 as a leap year (a legacy bug it keeps for compatibility), so serials before March 1900 can be off by a day. This never affects ordinary modern reporting dates — it is only a concern for historical data reaching back over a century.

Frequently asked questions

Why does my date format do nothing?

The cell holds a string. A date number_format only formats a stored date serial. Parse the string into a date/datetime object first, then write it.

How do I store a real date instead of text?

Assign a datetime.date or datetime.datetime object to the cell. openpyxl converts it to Excel's serial number automatically; cell.value reads back as a date object.

How do I show a date as dd/mm/yyyy?

Set cell.number_format = "dd/mm/yyyy". The format controls layout only; the stored serial is identical regardless of which code you choose.

Why does my date include a time of 00:00?

You wrote a datetime with no time, or used a code that includes hours. Use a date object, call .date() on a datetime, or pick a date-only code like mmm d, yyyy.

How do I format dates from a pandas DataFrame?

Ensure the column is datetime64 (convert with pd.to_datetime), export with to_excel, then reopen with openpyxl and set number_format on the date column.

Conclusion

Correct Excel dates are two layers: a real date/datetime value Excel stores as a serial, and a display code on top. Always write objects, never pre-formatted strings — a text date cannot be reformatted, sorted, or computed. Parse incoming strings with strptime or pd.to_datetime, strip timezones, and the format codes do the rest.

Where to go next

Applying Number and Date Formats in Excel — the parent cluster: separators, percentages, and the format-code reference table.
Format Excel Cells as Currency with Python — the currency companion to this guide.
Write a Pandas DataFrame to Excel Without the Index — keep your date column in the right place after export.