“Excel does not support datetimes with timezones”: Fixing It in pandas

Hello again, tech enthusiasts! 👋

You have a nice Python script. It downloads orders from a web system, cleans them with pandas, and saves a report for your manager. Then one morning it stops with this message:

ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.

Nothing changed in your code. So what happened? Usually, the data source started sending dates with a time zone. Today I will explain the problem in simple words and show you a fix that works for all your columns at once.

What you need

  • Python 3.9 or newer (https://www.python.org)
  • pandas and openpyxl: pip install pandas openpyxl
  • A table with date columns (we will make a small example)

Step 1: Understand the problem

In pandas, a date can be timezone-aware or timezone-naive.

  • A timezone-aware date knows its place in the world. For example: “10:00 in London”. It carries a label like +01:00 or UTC.
  • A timezone-naive date is just a clock time. For example: “10:00”. It does not say where.

Excel only knows naive dates. An Excel cell has no place to store a time zone. So pandas stops and asks you to choose. This is a good thing! pandas does not want to quietly change your times.

Let’s make the error happen on purpose:

import pandas as pd

df = pd.DataFrame({
    "order_id": [101, 102, 103],
    "created_at": pd.to_datetime(
        ["2025-07-01 08:30", "2025-07-01 14:05", "2025-07-02 23:50"]
    ).tz_localize("UTC"),
    "amount": [250.0, 99.5, 410.0],
})

print(df.dtypes)
df.to_excel("orders.xlsx", index=False)  # ValueError here

Look at the printed types. The created_at column shows datetime64[ns, UTC]. The UTC part is the time zone.

Step 2: Decide which clock you want

Before you remove the time zone, decide which local time your readers expect. This is the important step.

  • If your team works in New York, convert to New York time first.
  • If you just want the time as it is, remove the label only.

tz_convert moves the time to another zone. tz_localize(None) removes the time zone label and keeps the clock time.

# Option A: show the time in New York, then remove the label
df["created_at"] = df["created_at"].dt.tz_convert("America/New_York").dt.tz_localize(None)

# Option B: keep the UTC clock time, just remove the label
# df["created_at"] = df["created_at"].dt.tz_localize(None)

With Option A, “2025-07-01 08:30 UTC” becomes “2025-07-01 04:30”. With Option B, it stays “08:30”. Both are fine. Just choose on purpose, and maybe write the zone in the column name, like created_at_new_york.

Step 3: Fix all date columns automatically

Real reports often have many date columns: created, paid, shipped, updated. You do not want to fix them one by one. This function finds every timezone-aware column and fixes it.

import pandas as pd

def remove_timezones(df, target_tz=None):
    """Return a copy of df with no timezone-aware date columns.
    If target_tz is given (e.g. "America/New_York"), convert first."""
    df = df.copy()
    for col in df.columns:
        if isinstance(df[col].dtype, pd.DatetimeTZDtype):
            if target_tz:
                df[col] = df[col].dt.tz_convert(target_tz)
            df[col] = df[col].dt.tz_localize(None)
            print(f"Fixed column: {col}")

    # The index can have a time zone too
    if isinstance(df.index, pd.DatetimeIndex) and df.index.tz is not None:
        if target_tz:
            df.index = df.index.tz_convert(target_tz)
        df.index = df.index.tz_localize(None)
        print("Fixed the index")
    return df

pd.DatetimeTZDtype is the type pandas uses for timezone-aware date columns. So the isinstance check finds exactly the columns that cause the error.

Step 4: Write the Excel file

Now save the report. ExcelWriter lets you write more than one sheet into the same file, and set a nice date format.

clean = remove_timezones(df, target_tz="America/New_York")

with pd.ExcelWriter(
    "orders.xlsx",
    engine="openpyxl",
    datetime_format="yyyy-mm-dd hh:mm",
) as writer:
    clean.to_excel(writer, sheet_name="Orders", index=False)

print("Saved orders.xlsx")

Open the file. Your dates are real Excel dates, so you can sort and filter them. 🎉

Common problems

  • Error still happens, but no column is DatetimeTZDtype → Some columns have the type object and hold mixed values. Convert them first: df[col] = pd.to_datetime(df[col], utc=True), then run the function again.
  • TypeError: Cannot convert tz-naive timestamps → You called tz_convert on a column that has no time zone. Use tz_convert only on timezone-aware columns. The function above checks this for you.
  • Times are a few hours wrong → You probably used Option B when your readers expected local time. Use target_tz with your zone name.
  • Zone name not found → Use names like Europe/London or Asia/Tokyo, not short forms like “EST”.

Wrap-up

The error looks scary, but the fix is small. Excel cannot store a time zone, so you choose a local time, remove the label, and save. With the remove_timezones function, your report script will not break again when the data source changes.

Next idea: add the time zone name to the sheet title or a note cell, so readers always know which clock the report uses.

fsaeed

, , , ,