Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Write Dates Without the Time Part from pandas to Excel

pandas writes datetime columns with a YYYY-MM-DD HH:MM:SS format, so every date shows 00:00:00. Write real dates that display as dates: convert to date objects, set datetime_format on an xlsxwriter ExcelWriter, or apply a number format per column — and avoid the openpyxl engine option that is silently ignored.
Writing Excel filesDates & timesNumber & date formats

A DataFrame of order dates goes to Excel and every cell comes out like this:

Text
2026-03-01 00:00:00

The value is fine — it is a real Excel date at midnight — but the display shows a time nobody asked for. pandas holds dates in datetime64 columns, which always include a time of day, and to_excel writes them with the format YYYY-MM-DD HH:MM:SS. The fix is to change either what you write or how Excel is told to display it, and the right choice depends on whether the times carry information. This page belongs to Writing DataFrames to Excel with Pandas.

The same dates with the default and a date-only format Left: dates written from a datetime64 column show 2026-03-01 00:00:00 and 2026-03-02 00:00:00 because of the default date-time format. Right: written as dates, the same cells show 2026-03-01 and 2026-03-02 and remain real dates. A B 1 order_date amount 2 2026-03-01 00:00:00 120 3 2026-03-02 00:00:00 88 A B 1 order_date amount 2 2026-03-01 120 3 2026-03-02 88 default to_excel date-only display date-only format

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter
Python
import pandas as pd
from openpyxl import load_workbook

orders = pd.DataFrame({
    "order_date": pd.to_datetime(["2026-03-01", "2026-03-02", "2026-03-15"]),
    "shipped_at": pd.to_datetime(["2026-03-02 09:15", "2026-03-03 14:30", None]),
    "amount": [120, 88, 240],
})

def formats(path, row=2):
    ws = load_workbook(path).active
    return [(c.column_letter, c.number_format) for c in ws[row]]

formats reads back the number format of each cell in a row, which is how the rest of this page checks what was written.

Step 1 — Confirm what pandas writes

Python
orders.to_excel("default.xlsx", index=False)
print(formats("default.xlsx"))
# [('A', 'YYYY-MM-DD HH:MM:SS'), ('B', 'YYYY-MM-DD HH:MM:SS'), ('C', 'General')]

Both date columns get the date-and-time format. Normalising the times away with dt.normalize() does not change that: the column is still datetime64, so the cells still get the date-time format and still show 00:00:00. What matters to pandas is the column's type, not whether the times happen to be midnight.

Step 2 — Write Python date objects

The simplest fix works with either engine. Convert the column to Python date objects with .dt.date. pandas writes those with its date format, YYYY-MM-DD, and they remain real dates in Excel:

Python
out = orders.copy()
out["order_date"] = out["order_date"].dt.date
out.to_excel("dates.xlsx", index=False)
print(formats("dates.xlsx"))
# [('A', 'YYYY-MM-DD'), ('B', 'YYYY-MM-DD HH:MM:SS'), ('C', 'General')]

Use this when the time genuinely does not exist — order dates, invoice dates, birthdays. .dt.date drops the time, so apply it only to columns where losing the time is the intent. The column becomes object dtype in pandas, which is fine as the last step before writing but inconvenient if you keep calculating afterwards, so convert a copy.

Step 3 — Set the formats on the writer (xlsxwriter engine)

pd.ExcelWriter accepts date_format for date objects and datetime_format for datetime64 values. With the xlsxwriter engine they are applied to every date cell the writer produces:

Python
with pd.ExcelWriter("writer_formats.xlsx", engine="xlsxwriter",
                    datetime_format="DD/MM/YYYY", date_format="DD/MM/YYYY") as writer:
    orders.to_excel(writer, index=False)
print(formats("writer_formats.xlsx"))
# [('A', 'DD/MM/YYYY'), ('B', 'DD/MM/YYYY'), ('C', 'General')]

This is the least code when every date in the workbook should look the same. It is also blunt: shipped_at, which has real times, now hides them too. The values still contain 09:15 and 14:30 — only the display changed — which may or may not be what the report's readers expect.

Check the engine before relying on these arguments. In the pandas version tested here (3.0), the openpyxl writer accepts date_format and datetime_format without error and then does not apply them:

Python
with pd.ExcelWriter("ignored.xlsx", engine="openpyxl", datetime_format="DD/MM/YYYY") as writer:
    orders.to_excel(writer, index=False)
print(formats("ignored.xlsx"))
# [('A', 'YYYY-MM-DD HH:MM:SS'), ('B', 'YYYY-MM-DD HH:MM:SS'), ('C', 'General')]

No warning, no error, default formats. If your job uses the openpyxl engine — for example because it appends to an existing workbook — use Step 4 instead.

Which method changes what A comparison of four methods. .dt.date works with both engines, drops the time from the value, and applies to chosen columns. ExcelWriter datetime_format works with xlsxwriter only, keeps the time in the value, and applies to all date cells. Setting number_format with openpyxl works after writing with any engine, keeps the value, and applies to chosen columns. strftime produces text, which is not a real date. Works with openpyxl engine Keeps real dates Keeps the time Per column .dt.date ExcelWriter datetime_format number_format after writing strftime to text

Step 4 — Set a number format per column with openpyxl

For full control — dates in one column, date-times in another, a month-only display in a third — set number_format on the cells after pandas has written them. It works whichever engine wrote the file:

Python
orders.to_excel("per_column.xlsx", index=False)

wb = load_workbook("per_column.xlsx")
ws = wb.active
column_formats = {"order_date": "DD MMM YYYY", "shipped_at": "DD MMM YYYY HH:MM"}
headers = {cell.value: cell.column for cell in ws[1]}
for name, fmt in column_formats.items():
    col = headers[name]
    for (cell,) in ws.iter_rows(min_row=2, min_col=col, max_col=col):
        cell.number_format = fmt
    ws.column_dimensions[ws.cell(row=1, column=col).column_letter].width = 20
wb.save("per_column.xlsx")
print(formats("per_column.xlsx"))
# [('A', 'DD MMM YYYY'), ('B', 'DD MMM YYYY HH:MM'), ('C', 'General')]

Looking columns up by header name rather than letter keeps the code working when columns are added or reordered. Widening the columns matters more than it looks: a date formatted as DD MMM YYYY in a column too narrow for it shows as ########. The empty shipped_at cell — a missing value in pandas — is written as an empty cell, so formatting it does nothing visible.

Excel's format codes are not Python's strftime codes: MMM is the abbreviated month, MM after HH means minutes, and DD/YYYY are day and year. Format Dates in Excel Cells with Python lists the codes and their pitfalls.

Make it one call for every report

Jobs that write several workbooks benefit from wrapping Steps 2 and 4 in a function that takes a DataFrame and a mapping of column names to formats. Columns listed as date-only are converted to dates; every listed column gets its display format; everything else is left alone:

Python
def to_excel_with_dates(frame, path, formats, date_only=(), sheet_name="Sheet1"):
    out = frame.copy()
    for col in date_only:
        out[col] = out[col].dt.date
    out.to_excel(path, index=False, sheet_name=sheet_name)
    wb = load_workbook(path)
    ws = wb[sheet_name]
    headers = {cell.value: cell.column for cell in ws[1]}
    for name, fmt in formats.items():
        col = headers[name]
        for (cell,) in ws.iter_rows(min_row=2, min_col=col, max_col=col):
            cell.number_format = fmt
    wb.save(path)

to_excel_with_dates(orders, "report.xlsx",
                    formats={"order_date": "YYYY-MM-DD", "shipped_at": "YYYY-MM-DD HH:MM"},
                    date_only=["order_date"])
print(formats("report.xlsx"))

Choosing the format itself is a question about readers. YYYY-MM-DD is unambiguous everywhere and sorts correctly even when copied out as text, which makes it a good default for workbooks that cross borders. DD/MM/YYYY or MM/DD/YYYY suit a single-country audience; DD MMM YYYY is unambiguous and readable in any English-speaking locale. Whatever you pick, put it in the job's configuration rather than in code, so a change of audience does not need a change of program.

Step 5 — Do not convert dates to strings

It is tempting to format the column as text before writing:

Python
as_text = orders.assign(order_date=orders["order_date"].dt.strftime("%d/%m/%Y"))
as_text.to_excel("text_dates.xlsx", index=False)
print(load_workbook("text_dates.xlsx").active["A2"].data_type)
# 's' — a string, not a date

The cell shows what you wanted and is no longer a date. Excel sorts it alphabetically (so 02/04 comes before 15/03), cannot group it by month in a pivot table, and cannot subtract it from another date. Readers in other locales cannot tell 03/04 from 04/03 either. Keep real dates and change the format; that is what number formats are for.

A date cell versus a date-looking string Two panels. A real date with a date format sorts chronologically, filters and groups by month, works in date formulas and displays in the reader's preferred format. A strftime string looks the same but sorts alphabetically, cannot be grouped by month, breaks date formulas and is ambiguous across locales. Real date + number format sorts chronologically groups by month and year works in date formulas display can change later strftime() string sorts alphabetically no date grouping breaks date formulas 03/04 or 04/03?

Timezones and missing values

Two situations need attention before any of these steps. Timezone-aware columns cannot be written at all — Excel has no timezone concept — so convert them to local naive times first; Fix "Excel does not support datetimes with timezones" in pandas covers the conversion. Missing dates (NaT) are written as empty cells by every method on this page, which is usually what a report wants; if a placeholder such as "not shipped" is needed, fill it in a copy used only for output, because a text placeholder turns the column into text.

Common pitfalls

  • Normalising and expecting the format to change. dt.normalize() keeps the datetime64 type, so the date-time format is still used.
  • Relying on datetime_format with the openpyxl engine. It is accepted and ignored; verify the output's number_format.
  • Hiding real times with a date format. The time is still in the value and still affects filters that compare with a date.
  • Narrow columns. Long date formats in default-width columns show ########.

Performance notes

.dt.date creates Python objects for every value, which is slower than working with datetime64 but insignificant next to writing the file. Setting number_format cell by cell with openpyxl costs a few seconds per million cells; for very large exports, the xlsxwriter writer options (Step 3) apply formats while writing and add no time at all.

Conclusion

The 00:00:00 comes from pandas writing datetime64 columns with a date-time format. Convert columns whose time is meaningless with .dt.date, set datetime_format and date_format on an ExcelWriter when using the xlsxwriter engine, or apply number_format per column with openpyxl after writing — which works everywhere and leaves times intact where they matter. Never convert dates to strings to change how they look.

Frequently asked questions

Why does to_excel add 00:00:00 to my dates? pandas stores dates in datetime64 columns, which always carry a time. It writes those cells with the format YYYY-MM-DD HH:MM:SS, so a midnight time is displayed as 00:00:00 even though nothing is wrong with the value.

Does datetime_format work with the openpyxl engine? Not in the pandas version tested here (3.0). OpenpyxlWriter accepts date_format and datetime_format but does not apply them, so the default formats are used. Use the xlsxwriter engine for these options, or set number formats with openpyxl after writing.

Should I convert dates to strings with strftime? No. Strings look right but are text: Excel cannot sort them chronologically, filter them by month or use them in date formulas. Keep real dates and change the display format.

Does hiding the time with a format remove it? No. A format changes only the display. If the values contain real times such as 14:30, the cell still holds them. Use dt.normalize() or dt.date first when the time must go.

More on writing excel files

More on writing excel files →