Fixing Python xlsxwriter That Writes Datetime Values as Float Serial Numbers

You write a datetime object into an Excel cell with xlsxwriter, open the file, and see something like 45123.708333 staring back at you. The data is there — just completely unreadable. This is one of those bugs that feels obvious in hindsight but wastes real time the first time you hit it.

The fix is small, but it requires understanding why Excel behaves this way. Once you do, you will never make this mistake again.

What You'll Learn

• Why Excel stores dates as floating-point serial numbers internally
• How xlsxwriter's write_datetime() method works and what it actually writes
• How to attach a num_format to a cell format so Excel renders the value as a date
• How to handle timezone-aware datetime objects safely
• How to apply date formats across an entire column or a Pandas DataFrame export

Prerequisites

• Python 3.8 or later
• xlsxwriter installed (pip install xlsxwriter)
• Basic familiarity with Python's datetime module

How Excel Actually Stores Dates

Excel does not store dates as strings like "2024-03-15". Under the hood, every date and time is a floating-point number called a serial number. The integer part counts days since January 1, 1900 (day 1 in Excel's system), and the fractional part represents time within that day. So noon on January 1, 2024 is roughly 45292.5.

When you tell Excel to display that number as a date, you apply a number format — the same mechanism that turns 1234567.89 into $1,234,567.89. Without the format, Excel happily shows the raw float. That is exactly what happens when xlsxwriter writes your datetime object without a format attached.

This is not a bug in xlsxwriter. It is how the OOXML file format works, and xlsxwriter is faithfully producing a valid file. The missing piece is entirely on your side: you need to tell Excel how to render the value.

The Missing Piece: num_format

In xlsxwriter, every visual property of a cell — font, color, border, and number format — lives in a Format object that you create from the workbook. The num_format property is what controls date rendering. You set it once when creating the format, then pass that format object to every cell you write.

Here is the broken pattern that produces the float:

import xlsxwriter
from datetime import datetime

workbook = xlsxwriter.Workbook("report.xlsx")
worksheet = workbook.add_worksheet()

dt = datetime(2024, 3, 15, 14, 30, 0)

# No format passed — Excel will show a float serial number
worksheet.write_datetime(0, 0, dt)

workbook.close()

Open that file and cell A1 will show something like 45365.604166. The data is correct, but the presentation is broken.

Applying a Date Format Correctly

The fix is to create a Format object with a num_format string that matches the display pattern you want, then pass it as the fourth argument to write_datetime().

import xlsxwriter
from datetime import datetime

workbook = xlsxwriter.Workbook("report.xlsx")
worksheet = workbook.add_worksheet()

# Create the date format once and reuse it
date_format = workbook.add_format({"num_format": "yyyy-mm-dd"})
datetime_format = workbook.add_format({"num_format": "yyyy-mm-dd hh:mm:ss"})

worksheet.write_datetime(0, 0, datetime(2024, 3, 15), date_format)
worksheet.write_datetime(1, 0, datetime(2024, 3, 15, 14, 30, 0), datetime_format)

workbook.close()

Row 0 will display 2024-03-15 and row 1 will display 2024-03-15 14:30:00. The underlying serial number is unchanged — you have only told Excel how to render it.

Choosing a num_format string

The format string follows Excel's own date format syntax, not Python's strftime syntax. These are the tokens you will use most often:

Excel infers whether mm means month or minutes based on its position — after hh it means minutes, otherwise it means month. Keep that context rule in mind when building combined date-time formats.

Combining date format with other cell properties

You can mix num_format with any other format properties in the same dictionary. For instance, to also bold the cell and set a background color:

header_date_format = workbook.add_format({
    "num_format": "yyyy-mm-dd",
    "bold": True,
    "bg_color": "#D9E1F2",
    "align": "center",
})

Create format objects sparingly. xlsxwriter has a hard limit on the number of unique formats a workbook can contain. Define one format per style, then reuse it across all rows in that column.

Handling Timezone-Aware Datetimes

xlsxwriter's write_datetime() does not accept timezone-aware datetime objects. If you pass one, it raises a TypeError at runtime. You must strip the timezone before writing.

from datetime import datetime, timezone

aware_dt = datetime(2024, 3, 15, 14, 30, 0, tzinfo=timezone.utc)

# Convert to UTC and strip timezone info
naive_dt = aware_dt.replace(tzinfo=None)

worksheet.write_datetime(0, 0, naive_dt, datetime_format)

If your source data comes from a database or API with mixed timezones, normalize everything to UTC first using astimezone(timezone.utc), then strip with replace(tzinfo=None). Do this in your data-preparation step, not inside the write loop, so it is easy to audit.

Writing Date Ranges and DataFrames

Real reports rarely write a single datetime. More often you are iterating a list of rows or exporting a Pandas DataFrame. Both patterns need the same fix, just applied at scale.

Iterating a list of rows

import xlsxwriter
from datetime import datetime

rows = [
    (datetime(2024, 1, 1), "Sales", 12000),
    (datetime(2024, 2, 1), "Sales", 15400),
    (datetime(2024, 3, 1), "Sales", 11800),
]

workbook = xlsxwriter.Workbook("sales.xlsx")
worksheet = workbook.add_worksheet()

date_fmt = workbook.add_format({"num_format": "yyyy-mm-dd"})
money_fmt = workbook.add_format({"num_format": "#,##0"})

for row_idx, (date, category, amount) in enumerate(rows):
    worksheet.write_datetime(row_idx, 0, date, date_fmt)
    worksheet.write_string(row_idx, 1, category)
    worksheet.write_number(row_idx, 2, amount, money_fmt)

# Widen the date column so the full date is visible
worksheet.set_column(0, 0, 14)

workbook.close()

Notice the set_column() call at the end. Even with the correct format, if the column is too narrow, Excel renders ########. Set a sensible width whenever you write dates.

Exporting a Pandas DataFrame

When you use df.to_excel() with an xlsxwriter engine, Pandas handles the write calls internally, but you can still apply column formats via the underlying worksheet object.

import pandas as pd
from datetime import datetime

df = pd.DataFrame({
    "date": [datetime(2024, 1, 1), datetime(2024, 2, 1), datetime(2024, 3, 1)],
    "category": ["Sales", "Sales", "Sales"],
    "amount": [12000, 15400, 11800],
})

with pd.ExcelWriter("sales_df.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
    df.to_excel(writer, sheet_name="Report", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Report"]

    # Set column width so the date is fully visible
    worksheet.set_column(0, 0, 14)

The datetime_format parameter on pd.ExcelWriter is the cleanest way to control date rendering when you are using Pandas as the write layer. You can also set date_format for datetime.date objects. If you need that DataFrame export pattern to also survive index issues, the article on fixing Pandas DataFrame exports with wrong column order to Excel covers complementary DataFrame-to-Excel gotchas.

Common Pitfalls

Forgetting that write() does not call write_datetime()

xlsxwriter's generic write() method inspects the Python type of the value and dispatches to the right sub-method automatically. For datetime objects it does call write_datetime() — but it still needs you to pass a format. The dispatch does not conjure a format out of thin air. Always pass your format object explicitly.

Creating a new format object inside a loop

Each call to workbook.add_format() creates a new format entry in the workbook's internal table. Creating one inside a loop that writes thousands of rows will silently hit the format limit and corrupt the file. Define formats at the top of your script, once, and reuse them.

Using Python strftime tokens instead of Excel tokens

Python uses %Y, %m, %d. Excel uses yyyy, mm, dd. Mixing them produces garbled output or no formatting at all. If your format shows a literal %Y in the cell, that is the culprit.

Column too narrow to display the date

Even a perfectly formatted date will show ######## if the column is too narrow. Always call worksheet.set_column(col, col, width) with a width generous enough for your format string. A date like 2024-03-15 14:30:00 needs at least 20 characters of column width.

Writing datetime.date instead of datetime.datetime

datetime.date objects are also accepted by write_datetime(), but the same format rule applies. There is no automatic fallback. If you have a mix of date and datetime objects in the same column, normalize them to a single type before writing. You can also run into related issues when reading date columns back — the guide on fixing Pandas read_excel when it skips rows touches on how header position affects column type detection on import.

If you are working on a sheet with data validation rules alongside date columns, be aware that protected sheets add another layer of complexity — the article on fixing xlsxwriter data validation on protected sheets is worth a read before you finalize that workbook. And if you are also using freeze panes on a multi-sheet workbook, check out fixing xlsxwriter freeze_panes on workbooks with multiple sheets to avoid another common rendering trap.

Wrapping Up

The root cause is simple: xlsxwriter writes the correct numeric serial value for your datetime, but without a num_format the cell has no instruction telling Excel to display it as a date. The file is valid; the presentation is missing.

Here are the concrete actions to take right now:

1. Add num_format to every format object used on date columns. Use Excel's format token syntax (yyyy-mm-dd), not Python's strftime syntax.
2. Define format objects once at the top of your script, outside any loop, and pass them into each write_datetime() call.
3. Strip timezone info from aware datetimes before passing them to xlsxwriter to avoid a TypeError at runtime.
4. Call set_column() to give date columns enough width so Excel does not render ########.
5. If you are using Pandas, set datetime_format and date_format on the ExcelWriter constructor instead of managing per-cell formats manually.