Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Write Dates, Times and Durations with XlsxWriter

Write dates, times and durations to Excel correctly with XlsxWriter: why a date written without a format appears as 46297, setting a default date format, handling the timezone error by converting before stripping, durations over 24 hours with [h]:mm, times of day, dates before 1900, and the same settings through pandas' ExcelWriter.
xlsxwriterNumber & date formats

Excel has no date type. A date is a number of days since the end of 1899 — 2 October 2026 is 46297 — and a time is a fraction of a day, so 08:00 is 0.3333. What makes a cell look like a date is its number format. XlsxWriter converts Python datetime, date, time and timedelta objects to those numbers for you, but the formatting, timezones and a few edge cases are left to your code, and each produces a recognisable symptom: a column of five-digit numbers where dates should be, a TypeError about timezones, a shift duration of 37 hours showing as 13:05, or a historical date that turns into ########. This page goes through each with XlsxWriter directly and through pandas, checking what Excel will display with a LibreOffice export. It belongs to Building Excel Reports with xlsxwriter.

Prerequisites

Bash
pip install xlsxwriter pandas

The examples were run with XlsxWriter 3.2. LibreOffice (soffice) is used to export each cell as displayed; a small helper wraps that:

Python
import subprocess
from pathlib import Path

def displayed(path):
    subprocess.run(["soffice", "--headless", "--convert-to", "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true",
                    "--outdir", "calc", path], check=True, capture_output=True)
    return Path("calc", Path(path).stem + ".csv").read_text(encoding="utf-8").splitlines()

Step 1 — A date needs a format

Python
import datetime as dt
import xlsxwriter

wb = xlsxwriter.Workbook("dates.xlsx")
ws = wb.add_worksheet()
date_fmt = wb.add_format({"num_format": "dd mmm yyyy"})
stamp_fmt = wb.add_format({"num_format": "yyyy-mm-dd hh:mm"})
ws.write(0, 0, dt.date(2026, 10, 2))                          # no format
ws.write(1, 0, dt.date(2026, 10, 2), date_fmt)
ws.write_datetime(2, 0, dt.datetime(2026, 10, 2, 8, 0), stamp_fmt)
ws.write(3, 0, dt.datetime(2026, 10, 2, 8, 0))                # no format
ws.set_column(0, 0, 18)
wb.close()
print(displayed("dates.xlsx"))

XlsxWriter turns the date into its serial number either way, but only the cells with a date format look like dates: the unformatted ones show 46297 and 46297.3333 — the classic symptom. write() recognises date and datetime objects and hands them to write_datetime(), so either call works; the format is what matters. Choosing an unambiguous display format such as dd mmm yyyy or ISO yyyy-mm-dd avoids the day/month confusion between US and European readers.

To avoid passing a format to every call, set a workbook default. It applies to dates written without an explicit format:

Python
wb = xlsxwriter.Workbook("default_fmt.xlsx", {"default_date_format": "dd mmm yyyy"})
ws = wb.add_worksheet()
ws.write(0, 0, dt.date(2026, 10, 2))
ws.write(1, 0, dt.datetime(2026, 10, 2, 8, 0))
wb.close()
print(displayed("default_fmt.xlsx"))

Both cells now display as dates without any per-cell format, which keeps report code shorter when most dates share one style. The default applies to times too, so a timestamp written this way shows only its date — set an explicit format where the time matters.

What XlsxWriter writes, and what the format shows The date 2 October 2026 is stored as 46297 and 08:00 the same day as 46297.3333. Without a number format the cell displays the serial number; with dd mmm yyyy it displays 02 Oct 2026. A B C D 1 Python value Stored 2 date(2026 10 2) 46297 3 datetime(…, 8, 0) 46297.3333 A B 1 No format With format 2 46297 02 Oct 2026 3 46297.3333 2026-10-02 08:00 before after number format

Step 2 — Timezones: convert, then strip

Excel cells have no timezone, so XlsxWriter refuses timezone-aware datetimes rather than guess:

Python
from zoneinfo import ZoneInfo

utc_event = dt.datetime(2026, 10, 2, 7, 30, tzinfo=ZoneInfo("UTC"))
wb = xlsxwriter.Workbook("tz.xlsx")
ws = wb.add_worksheet()
try:
    ws.write_datetime(0, 0, utc_event, stamp_fmt)
except TypeError as e:
    print("TypeError:", e)

local = utc_event.astimezone(ZoneInfo("Europe/London")).replace(tzinfo=None)
fmt = wb.add_format({"num_format": "yyyy-mm-dd hh:mm"})
ws.write_datetime(0, 0, local, fmt)
ws.write(0, 1, "Europe/London")
wb.close()

wb = xlsxwriter.Workbook("tz_removed.xlsx", {"remove_timezone": True})
ws = wb.add_worksheet()
ws.write_datetime(0, 0, utc_event, wb.add_format({"num_format": "yyyy-mm-dd hh:mm"}))
wb.close()
print("converted:", displayed("tz.xlsx"), "| remove_timezone:", displayed("tz_removed.xlsx"))

The error message points at the remove_timezone workbook option, which simply drops the timezone and keeps the clock time — 07:30 UTC is written as 07:30. That is right only if your readers think in UTC. If they think in local time, convert first with astimezone() and then remove the timezone: the same event becomes 08:30, British Summer Time. Write the zone in a header or an adjacent column so nobody has to guess which clock the column uses. The same decision appears in pandas, covered in Fix "Excel does not support datetimes with timezones" in pandas.

Step 3 — Durations and times of day

Python
wb = xlsxwriter.Workbook("durations.xlsx")
ws = wb.add_worksheet()
dur_wrong = wb.add_format({"num_format": "hh:mm"})
dur_right = wb.add_format({"num_format": "[h]:mm"})
tod = wb.add_format({"num_format": "hh:mm"})
shift = dt.timedelta(hours=37, minutes=5)
ws.write_datetime(0, 0, shift, dur_wrong)
ws.write_datetime(0, 1, shift, dur_right)
ws.write_datetime(0, 2, dt.time(14, 45), tod)
ws.write_number(0, 3, shift / dt.timedelta(hours=1), wb.add_format({"num_format": "0.00"}))
wb.close()
print(displayed("durations.xlsx"))

A timedelta is written as a number of days — 37 hours 5 minutes is 1.545 — and a time as a fraction of a day. Formatted with hh:mm, the duration shows 13:05: the hours wrap at 24 like a clock. The square brackets in [h]:mm make the hours elapsed and they keep counting, showing 37:05. For timesheets and SLA reports that difference is the whole point. When a duration is needed as decimal hours — for multiplying by an hourly rate — write it as hours, as column D does with shift / timedelta(hours=1), giving 37.08; in a worksheet formula the equivalent is multiplying the duration cell by 24. Durations beyond the cell level are covered in Format Durations Over 24 Hours in Excel.

hh:mm or [h]:mm for a duration A duration of 37 hours 5 minutes is stored as 1.545 days. With hh:mm the hours wrap at 24 and it shows 13:05. With [h]:mm the hours keep counting and it shows 37:05. Multiplying by 24 gives decimal hours, 37.08. hh:mm (clock) 37 h 5 min → 13:05 wraps at 24 hours right for times of day [h]:mm (elapsed) 37 h 5 min → 37:05 keeps counting right for durations

Step 4 — Dates before 1900 and dates from pandas

Excel's date system starts on 1 January 1900; it cannot represent earlier dates. XlsxWriter writes them anyway, as negative serial numbers, which Excel displays as ########:

Python
wb = xlsxwriter.Workbook("old.xlsx")
ws = wb.add_worksheet()
ws.write_datetime(0, 0, dt.datetime(1899, 12, 25), wb.add_format({"num_format": "yyyy-mm-dd"}))
ws.write_string(0, 1, "1899-12-25")
wb.close()

import zipfile
print(zipfile.ZipFile("old.xlsx").read("xl/worksheets/sheet1.xml").decode().split('<c r="A1"')[1][:30])

The stored value is -6 — six days before the start of Excel's calendar. Excel shows hashes, because it has no way to display a negative date; LibreOffice, whose own date system reaches further back, shows a date one day off because of the historical 1900 leap-year quirk. For genealogy, archive or historical data, write such dates as ISO text strings, as in column B, and note that they will not sort or filter as dates.

Through pandas, the same choices are made on the ExcelWriter:

Python
import pandas as pd

df = pd.DataFrame({"start": pd.to_datetime(["2026-10-02 08:00", "2026-10-03 21:15"]),
                   "day": pd.to_datetime(["2026-10-02", "2026-10-03"]).date,
                   "duration": pd.to_timedelta(["37:05:00", "07:30:00"])})
with pd.ExcelWriter("pandas_dates.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd hh:mm", date_format="dd mmm yyyy") as xw:
    df.to_excel(xw, index=False)
print("timedelta as written by pandas:", displayed("pandas_dates.xlsx"))

df["duration"] = df["duration"] / pd.Timedelta(days=1)            # fraction of a day, a plain float
with pd.ExcelWriter("pandas_dates.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd hh:mm", date_format="dd mmm yyyy") as xw:
    df.to_excel(xw, index=False)
    xw.sheets["Sheet1"].set_column(2, 2, 10, xw.book.add_format({"num_format": "[h]:mm"}))
print("as days with a column format:", displayed("pandas_dates.xlsx"))

datetime_format applies to datetime columns and date_format to date objects. Durations are the trap: pandas writes a timedelta column as a number of days with the cell format 0, so 37 hours 5 minutes appears as 2 and 7½ hours as 0 — and because the format is on each cell, a column format cannot override it. Converting the column to a plain fraction of a day first, by dividing by pd.Timedelta(days=1), leaves the cells unformatted, so a [h]:mm column format added through the underlying XlsxWriter workbook (xw.book) takes effect and the durations read 37:05 and 7:30. A timezone-aware pandas column raises its own error at this point — convert with dt.tz_convert(...) and then dt.tz_localize(None) first, the pandas equivalent of Step 2.

Getting durations from pandas into Excel pandas writes a timedelta column as days with the cell format 0, so 37 hours shows as 2. Dividing the column by one day turns it into plain floats without a cell format, and a [h]:mm column format added through the XlsxWriter workbook then displays 37:05. timedelta column 37:05:00 to_excel as is cell format 0 → "2" ÷ Timedelta(days=1) plain float 1.545 [h]:mm column format shows 37:05

Whichever route writes the file, a quick test that reads the workbook back with openpyxl and checks cell.number_format on a date, a timestamp and a duration column catches all of these problems before a report goes out: an unformatted date shows up as the format General, a wrapped duration as hh:mm without brackets, and a pandas duration as 0.

Common pitfalls

  • Dates showing as 46297. Give the cell a date format, or set default_date_format.
  • remove_timezone without converting. It keeps the clock time; convert to the readers' zone first.
  • Durations with hh:mm. Use [h]:mm so hours do not wrap at 24.
  • Dates before 1900. Excel cannot show them; write ISO text.
  • pandas durations as decimals. Add a [h]:mm column format through xw.book.

Conclusion

XlsxWriter stores dates, times and durations as Excel serial numbers, and a number format makes them readable: pass a format to write_datetime() or set default_date_format. Convert timezone-aware datetimes to the readers' zone before stripping the timezone, format durations with [h]:mm so they do not wrap, write dates before 1900 as text, and in pandas use datetime_format and date_format on the ExcelWriter, adding duration formats through the workbook object.

Frequently asked questions

Why do my dates appear as numbers like 46297 in Excel? The cell has no date number format. Pass a format to write_datetime or set the workbook's default_date_format option.

How do I fix "Excel doesn't support timezones in datetimes" in XlsxWriter? Convert the datetime to the timezone your readers use and remove tzinfo, or use the remove_timezone workbook option if the clock time can be kept as it is.

How do I write a duration longer than 24 hours? Write a timedelta with write_datetime and format it with h:mm or h:mm:ss so the hours do not wrap.

Can Excel store dates before 1900? No. Excel's date system starts in 1900, so write earlier dates as text.

More on xlsxwriter

More on xlsxwriter →