Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

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.

A Python date is stored as an Excel serial and displayed through a format code A Python date object is stored as an Excel serial number, then rendered two ways depending on the chosen display format code. Python date date(2024,1,5) Excel serial 45296 days from base "yyyy-mm-dd" 2024-01-05 "mmm d, yyyy" Jan 5, 2024 number_format picks the look

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.

A text date ignores a date format, but a real date serial obeys it Two lanes compared. In the top lane a string cell keeps showing its raw text when a date format is applied. In the bottom lane a Python date is stored as an Excel serial and re-renders in the chosen format. Stored value Date format applied Excel displays "2024-03-09" str · no date serial mmm d, yyyy applied, but ignored 2024-03-09 unchanged — still text ✕ date(2024, 3, 9) real serial · 45360 mmm d, yyyy applied to serial Mar 9, 2024 reformatted correctly ✓
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

SymptomCauseFix
Date format ignoredCell holds a string, not a date serialParse with datetime.strptime/pd.to_datetime before writing
Cannot sort or filter as datesValues are textConvert to real date/datetime objects
Time component shows when you want a dateWrote a datetime, not a dateUse dt.date(), or a date-only code like yyyy-mm-dd
ValueError writing a tz-aware datetimeExcel has no timezone conceptStrip tzinfo: dt.replace(tzinfo=None) (after converting to your target zone)
Off-by-one near early-1900 datesExcel's 1900 leap-year quirkAvoid 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.

Performance and scale notes

Assigning number_format per cell is cheap, but on large date columns two habits keep it fast:

  • Set the code in the same loop that writes the values. Iterate rows once and assign both cell.value and cell.number_format together, rather than writing everything and re-scanning the column afterwards. On tens of thousands of rows a single pass roughly halves the work.
  • Reuse one string, and prefer a named style for repetition. A number_format is just a string, so define it once (DATE_CODE = "yyyy-mm-dd") and assign the same value everywhere. When the same date look repeats across many cells, register a NamedStyle once and apply it by name — openpyxl then stores the format a single time instead of duplicating it, which shrinks the file and speeds saving.
Python
from datetime import date, timedelta
from openpyxl import Workbook
from openpyxl.styles import NamedStyle

wb = Workbook()
ws = wb.active

iso_date = NamedStyle(name="iso_date", number_format="yyyy-mm-dd")
wb.add_named_style(iso_date)

start = date(2024, 1, 1)
for i in range(50_000):
    ws.cell(row=i + 1, column=1, value=start + timedelta(days=i)).style = "iso_date"

wb.save("dates_bulk.xlsx")
print("Wrote 50k dates with one shared style")

For pandas exports that already carry a datetime64 column, let to_excel write the serials in bulk and only reopen with openpyxl to retag the format — reopening once and looping a single column is far cheaper than rebuilding the sheet cell by cell. If you are streaming very large sheets, openpyxl's write-only workbook mode still accepts date/datetime values, but you attach the format through a styled cell rather than editing number_format after the fact.

Write the value, then the format

The two halves are independent and both are required: a real datetime in the cell, and a date number format on it. A date written as text ignores the format entirely and sorts alphabetically; a date value without a format shows as a five-digit serial number. Setting both together, in the finishing pass over the sheet, is the only combination that behaves the way readers expect.

Use ISO in generated files

yyyy-mm-dd sorts correctly, is unambiguous across locales, and needs no explanation.

Value and format together

The two halves of a formatted date The cell must hold a real date value and carry a date number format. Missing either one produces a serial number or a text string that cannot be sorted. datetime + yyyy-mm-dd sorts and filters as a date reads unambiguously what you want text, or no format 45678, or a string no date filters looks broken

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