Formatting Dates in Excel with openpyxl

When you write dates to Excel with Python, they show up as raw serial numbers by default. Excel stores dates as numbers internally, so the value is correct but the display format is missing.

The problem

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active
ws.append(["2024-03-15"])
wb.save("output.xlsx")

Open that file and you'll see 45380. The fix is applying a number_format to the cells.

Fix it with number_format

Set the format directly on each cell. The value must be a datetime or date object, not a string.

from datetime import date
import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

ws.append(["Order Date", "Shipped Date"])
ws.append([date(2024, 1, 15), date(2024, 2, 28)])
ws.append([date(2024, 3, 1), date(2024, 3, 15)])

for row in ws.iter_rows(min_row=2, max_col=2):
    for cell in row:
        cell.number_format = "YYYY-MM-DD"

wb.save("orders.xlsx")

number_format uses the same format codes as Excel's Format Cells dialog. The date is stored as a number, but Excel displays it in the format you specify.

Common format codes

Format code	Displays as
YYYY-MM-DD	2024-03-15
MM/DD/YYYY	03/15/2024
DD-MMM-YYYY	15-Mar-2024
MMM YYYY	Mar 2024
MMMM DD, YYYY	March 15, 2024
YYYY	2024
MM/DD/YYYY HH:MM	03/15/2024 14:30

Reusable styles with NamedStyle

Define a style once and apply it to any cell in the workbook. Register it once with add_named_style (calling it again raises a ValueError).

from datetime import date
from openpyxl.styles import NamedStyle

wb = openpyxl.Workbook()
date_style = NamedStyle(name="iso_date", number_format="YYYY-MM-DD")
wb.add_named_style(date_style)

for sheet_name in ["Jan", "Feb", "Mar"]:
    ws = wb.create_sheet(sheet_name)
    ws.append(["Date", "Amount"])
    ws.append([date(2024, 1, 15), 1200])
    ws.append([date(2024, 1, 16), 3400])
    ws["A2"].style = date_style
    ws["A3"].style = date_style

del wb["Sheet"]
wb.save("quarterly.xlsx")

With pandas

df.to_excel() accepts a date_format parameter in pandas 2.x but this was removed in pandas 3.0. To format dates in pandas 3.0+, write the DataFrame and then apply formatting with openpyxl.

import pandas as pd
from openpyxl import load_workbook
from datetime import datetime

df = pd.DataFrame({
    "date": pd.date_range("2024-01-01", periods=5),
    "value": [10, 20, 30, 40, 50],
})

df.to_excel("report.xlsx", index=False)

# Apply date formatting after writing
wb = load_workbook("report.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, max_col=1):
    row[0].number_format = "YYYY-MM-DD"
wb.save("report.xlsx")

With polars

Polars uses xlsxwriter under the hood for Excel export. Install it with pip install xlsxwriter. After writing, open the file with openpyxl to apply formatting.

import polars as pl
from datetime import date
from openpyxl import load_workbook

dates = pl.date_range(date(2024, 1, 1), date(2024, 1, 5), eager=True)
df = pl.DataFrame({"date": dates, "value": [10, 20, 30, 40, 50]})

df.write_excel("report.xlsx")

wb = load_workbook("report.xlsx")
ws = wb.active
ws["A2"].number_format = "YYYY-MM-DD"
wb.save("report.xlsx")

pl.date_range() returns a lazy expression by default. Pass eager=True to evaluate it immediately and get a Series.

Mixed formats in one sheet

Different columns can use different formats. A realistic example for a financial report:

from datetime import date
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Transactions"

header_font = Font(bold=True)
thin_border = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)

headers = ["Date", "Description", "Amount"]
data = [
    [date(2024, 1, 15), "Office supplies", 245.00],
    [date(2024, 2, 28), "Consulting fee", 3500.00],
    [date(2024, 3, 1), "Software license", 1200.00],
    [date(2024, 6, 30), "Annual renewal", 960.00],
]

ws.append(headers)
for row in data:
    ws.append(row)

for cell in ws[1]:
    cell.font = header_font
    cell.border = thin_border
    cell.alignment = Alignment(horizontal="center")

for row in ws.iter_rows(min_row=2, max_row=len(data) + 1):
    row[0].number_format = "DD-MMM-YYYY"
    row[2].number_format = '$#,##0.00'
    for cell in row:
        cell.border = thin_border

ws.column_dimensions["A"].width = 16
ws.column_dimensions["B"].width = 20
ws.column_dimensions["C"].width = 14

wb.save("transactions.xlsx")

Date column gets DD-MMM-YYYY, amount column gets currency format. The rest is standard spreadsheet styling.

Reading formatted dates back

openpyxl returns the raw datetime object regardless of display format. The format is for display only.

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    print(f"{row[0]} | {row[1]}")

Output:

2024-01-15 00:00:00 | 2024-02-28 00:00:00
2024-03-01 00:00:00 | 2024-03-15 00:00:00

To get the formatted string instead:

cell = ws["A2"]
if cell.value:
    print(cell.value.strftime("%Y-%m-%d"))