Why an Exported Excel Date Shows as a Number and the Fix

The sheet opens, and the column that should read 2024-01-01 reads 45292. Nothing is lost; the data is correct and the display is wrong. This is the most common complaint about Excel exports, and the explanation is short once you know how the format works. This post explains the mechanism, lists the ways code ends up writing a number where a date belongs, shows the fix for each, and repairs a file already sent. It is a spoke of sending database results to Excel.

The mechanism

Excel has no date type. A cell holds a number, and a date is a number of days since an epoch, with the time of day as the fraction of a day. In the default 1900 system, 45292 is 1 January 2024, and 45292.5 is noon on that day (how Excel stores dates as serial numbers). What makes a cell look like a date is its number format, a pattern such as yyyy-mm-dd. The XML for a date cell has the number and a style index; the style says "format as a date":

<c r="C2" s="1"><v>45292</v></c>

Remove the s="1", or point it at a style whose format is General, and the same cell shows 45292. That is what an export bug looks like inside the file: a correct number with no date format. The reverse also happens: a cell with a date format but a text value shows the text unchanged, since the format applies only to numbers.

How exports end up with numbers

A writer that does not know the column is a date. If a column of dates arrives as numbers already, nothing in the writer can tell. This happens when a pipeline converts dates to serial numbers or to epoch seconds and forgets to convert back. A date stored as a Unix timestamp (1704067200) is a number to every writer, and is also a very different number from an Excel serial.

**A column of Python date objects in a pandas object column.** pandas recognizes datetime64 columns and writes them as dates. A column of datetime.date objects has object dtype, and whether it is written with a date format depends on the pandas and engine versions. Convert explicitly to be safe.

Strings. A column of text like 2024-01-01 is text. It displays as written and does not sort or calculate as a date. This one does not show as a number, but it is the sibling problem: Excel may convert it to a date on edit, or not.

Direct low-level writes. Writing 45292 yourself with write_number and no format gives exactly this symptom. Writing a Python datetime with write_datetime and a date format gives the right result. XlsxWriter's write_datetime requires a format to show a date: without one, the cell holds the right serial number and displays it as a number.

Format lost on edit. Some tools that copy values and drop styles (a copy of cells into a new workbook without formats) reproduce the symptom.

The fix in pandas

Make the column a real datetime, then give the writer a format:

import pandas as pd

df["placed"] = pd.to_datetime(df["placed"])

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

datetime_format applies to datetime cells and date_format to date cells. With these set, the cells carry a date format, and Excel shows 2024-01-01 09:15. Without them, pandas uses its own default format for datetimes (YYYY-MM-DD HH:MM:SS), which is why a pandas export usually shows dates correctly even when you did nothing, and why the number symptom points at a column that was not a datetime.

If the column holds Unix timestamps, convert them first: pd.to_datetime(df["ts"], unit="s"). If it holds Excel serials from another source, convert with the epoch: pd.to_datetime(df["serial"], unit="D", origin="1899-12-30") (converting Excel serial dates in Python).

The fix in openpyxl and XlsxWriter

In openpyxl, assign a datetime object and set the format, since the library assigns a default date format to a datetime but not to a bare number:

from datetime import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = datetime(2024, 1, 1, 9, 15)
print(ws["A1"].number_format)
ws["A1"].number_format = "yyyy-mm-dd hh:mm"

yyyy-mm-dd h:mm:ss

Assigning a datetime makes openpyxl choose its default date-time format. You can override it as shown. If you write a number, the format stays General, and the cell displays the number. In XlsxWriter, create a format and pass it:

import xlsxwriter
from datetime import datetime

wb = xlsxwriter.Workbook("dates.xlsx")
ws = wb.add_worksheet()
fmt = wb.add_format({"num_format": "yyyy-mm-dd hh:mm"})
ws.write_datetime(0, 0, datetime(2024, 1, 1, 9, 15), fmt)
ws.set_column("A:A", 18, fmt)
wb.close()

The cell holds 45292.40625 (9:15 is 0.40625 of a day) and displays 2024-01-01 09:15. Setting the format on the column as well covers rows a reader adds later. Make the column wide enough for the pattern: a date-time needs about 16 characters, and a narrow column shows #### for a valid date, which is another symptom that gets mistaken for an error.

Repair a file that is already out

You do not need to re-export. In Excel, select the column, open Format Cells, and choose a date category or a custom pattern. The numbers become dates immediately. If the column holds Unix timestamps or any value that is not an Excel serial, a format will not help: the numbers are not dates in Excel's terms, and you need a conversion formula such as =A2/86400+DATE(1970,1,1) for epoch seconds, formatted as a date.

In code, to fix a workbook in place with openpyxl:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Orders"]
for row in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    for cell in row:
        cell.number_format = "yyyy-mm-dd"
wb.save("report_fixed.xlsx")

Column 3 gets the date format. Note that loading and saving with openpyxl can lose features it does not support (some charts, images and form controls are not preserved), so save to a new file and compare (editing an existing workbook without losing formatting).

When dates look like numbers for a different reason

A formula result. The difference between two dates is a number of days, and Excel formats the result of =B2-A2 as a date if it can, giving a bizarre date in January 1900. Format the result as a number or [h]:mm. The reverse, a cell with =A2+30, usually inherits the date format.

A pivot table or chart. The grouping of dates can display the underlying serial numbers in an axis or a cell. Format the field.

A CSV opened in Excel. If the CSV contained serial numbers, Excel shows numbers. If it contained text dates, Excel may convert them to dates, according to the locale, or leave them as text.

A date system mismatch. If the dates display as dates but are four years off, the workbook uses the 1904 system (Mac and Windows date systems in Excel). That is a different bug with a different fix.

The cell is text with a number inside. A cell formatted as text containing 45292 shows 45292, and applying a date format does nothing until the content is re-entered or converted.

Prevent it with a test

A one-line assertion after writing catches the problem before delivery. Read the file back and check that the date columns have a date format:

from openpyxl import load_workbook
from openpyxl.utils.datetime import from_excel

ws = load_workbook("report.xlsx")["Orders"]
cell = ws["C2"]
assert cell.is_date, f"C2 is not formatted as a date: {cell.number_format!r}"

cell.is_date is true when the number format looks like a date. Run the same check on the first data row of every date column. It takes a few seconds and prevents the e-mail that begins "the dates are wrong".

When a date showing as a number is not the cause

The column holds numbers on purpose. An elapsed-days or a duration column legitimately holds numbers. Label it and format it as a number.

Two date systems are mixed. A merge of workbooks from different sources can combine 1900 and 1904 serials. Convert before combining.

The source system exported serial numbers. Some systems emit Excel-style serials as numbers by design. The fix is on their side, or a conversion step on yours.

The reader's locale shows the date in a form they do not recognize. A date shown as 01/03/2024 can be January 3 or March 1. Use an unambiguous pattern such as yyyy-mm-dd.

The format codes are documented in Microsoft's guide to number format codes, and the library behavior in the openpyxl page on working with dates and times. To check a small extract as JSON, use the CSV to JSON converter.