Python Doc & Data Automation

Fix Excel Does Not Support Timezones Error

Writing a report that came from a database or an API fails at the export step:

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

The same frame writes to CSV and Parquet without complaint, and the column looks unremarkable:

>>> orders["created_at"].head(2)
0   2026-09-17 08:14:22+00:00
1   2026-09-17 09:02:41+00:00
Name: created_at, dtype: datetime64[ns, UTC]

Root Cause

Excel stores a date as a number of days since its epoch, with the fractional part as the time of day. There is no field for a UTC offset or a timezone name, so an aware timestamp cannot be represented without losing information — and pandas refuses rather than silently choosing which information to discard. That refusal is a feature: the two reasonable interpretations differ. Writing the UTC wall clock shows 08:14 for an order that a London user placed at 09:14 British Summer Time, while converting to local time first shows the time they recognise but loses the offset. A third trap sits behind both: after tz_localize(None), the values look identical to naive timestamps that were always local, so a later reader cannot tell which convention a column follows unless the report says so.

Minimal Diagnostic

Find every timezone-aware column and show what each interpretation would produce, so the choice is explicit.

# pip install "pandas>=2.2"
import pandas as pd

REPORTING_TZ = "Europe/London"

def timezone_report(frame: pd.DataFrame, reporting_tz: str = REPORTING_TZ) -> None:
    aware = [c for c in frame.columns if isinstance(frame[c].dtype, pd.DatetimeTZDtype)]
    naive = [c for c in frame.select_dtypes("datetime64[ns]").columns]
    print(f"timezone-aware columns: {aware or 'none'}")
    print(f"naive datetime columns: {naive or 'none'}")
    for col in aware:
        sample = frame[col].dropna().head(3)
        local = sample.dt.tz_convert(reporting_tz)
        print(f"\n{col} (dtype {frame[col].dtype}):")
        for utc_value, local_value in zip(sample, local):
            print(f"  stored {utc_value}  ->  {reporting_tz}: {local_value:%Y-%m-%d %H:%M}"
                  f"  |  UTC wall clock: {utc_value.tz_localize(None):%Y-%m-%d %H:%M}")
        offsets = frame[col].dt.tz_convert(reporting_tz).map(lambda t: t.utcoffset()).dropna().unique()
        print(f"  distinct offsets in {reporting_tz}: {[str(o) for o in offsets]}")

if __name__ == "__main__":
    orders = pd.read_parquet("in/orders.parquet")
    timezone_report(orders)

timezone-aware columns: ['created_at', 'shipped_at']
naive datetime columns: ['order_date']

created_at (dtype datetime64[ns, UTC]):
  stored 2026-09-17 08:14:22+00:00  ->  Europe/London: 2026-09-17 09:14  |  UTC wall clock: 2026-09-17 08:14
  stored 2026-11-03 08:14:22+00:00  ->  Europe/London: 2026-11-03 08:14  |  UTC wall clock: 2026-11-03 08:14
  distinct offsets in Europe/London: ['1:00:00', '0:00:00']

The two offsets confirm the report spans a daylight-saving change: rows from September are an hour ahead of UTC, rows from November are not. Writing UTC wall clocks would shift September's times by an hour relative to what users saw.

Which conversion a column needs The root asks what readers of the report do with the timestamp. If they compare it with their own clocks, convert to the reporting timezone and drop the offset, labelling the column accordingly. If they compare it with system logs or other UTC data, convert to UTC and label the column as UTC. If both matter, write the local time as a datetime and the original instant as a separate text column so nothing is lost. What will readers compare it with? their clock, logs, or both their own clock Local time tz_convert then tz_localize(None) system logs (UTC) Keep UTC convert to UTC then drop both Two columns local + ISO instant Label the column created_at_london Label the column created_at_utc

Fix: Convert to the Reporting Timezone, Then Drop the Offset

Make the conversion explicit, rename the column so its meaning travels with it, and keep the original instant available for anyone who needs it. Changed lines carry comments.

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

REPORTING_TZ = "Europe/London"

def excel_ready(frame: pd.DataFrame, reporting_tz: str = REPORTING_TZ,
                keep_instant: bool = True) -> pd.DataFrame:
    out = frame.copy()
    for col in list(out.columns):
        if not isinstance(out[col].dtype, pd.DatetimeTZDtype):
            continue
        local = out[col].dt.tz_convert(reporting_tz)                    # changed: to the reader's clock
        if keep_instant:
            out[f"{col}_utc_iso"] = out[col].dt.tz_convert("UTC").dt.strftime("%Y-%m-%dT%H:%M:%SZ")
        out[col] = local.dt.tz_localize(None)                            # changed: drop the offset last
        suffix = reporting_tz.split("/")[-1].lower()
        out = out.rename(columns={col: f"{col}_{suffix}"})               # changed: meaning in the name
    return out

def write_report(frame: pd.DataFrame, dest: Path, sheet: str = "Orders") -> Path:
    ready = excel_ready(frame)
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter",
                        datetime_format="yyyy-mm-dd hh:mm") as writer:    # changed: one display format
        ready.to_excel(writer, sheet_name=sheet, index=False)
        ws = writer.sheets[sheet]
        for i, col in enumerate(ready.columns):
            width = max(len(str(col)) + 2, 19 if "datetime" in str(ready[col].dtype) else 12)
            ws.set_column(i, i, width)
        ws.freeze_panes(1, 0)
    return dest

if __name__ == "__main__":
    orders = pd.read_parquet("in/orders.parquet")
    print(write_report(orders, Path("out/orders.xlsx")))

The order of operations matters: tz_convert first changes the instant's representation to the target zone, then tz_localize(None) removes the offset while keeping those local wall-clock values. Doing it the other way round — tz_localize(None) on a UTC column and then treating it as local — silently shifts every value by the offset, which is the most common way this "fix" introduces a bug.

Renaming to created_at_london costs nothing and answers the question every recipient eventually asks. The ISO instant column keeps the export lossless: anyone reconciling with system logs can read it back with pd.to_datetime(frame["created_at_utc_iso"], utc=True).

Convert then drop, or drop then assume Converting first turns 08:14 UTC into 09:14 London time and then drops the offset, so the exported value matches what the user saw. Dropping the offset first leaves 08:14 and labels it as London time, which is an hour early for every row during British Summer Time and correct only in winter. The difference disappears and reappears across daylight-saving changes, which makes it hard to spot in a single sample. Drop first (wrong) stored 2026-09-17 08:14+00:00 tz_localize(None) -> 08:14 labelled 'local time' user saw 09:14 one hour early in summer Convert then drop stored 2026-09-17 08:14+00:00 tz_convert('Europe/London') -> 09:14 tz_localize(None) -> 09:14 user saw 09:14 correct all year

Variant Fix 1: Mixed and Per-Row Timezones

Data from several regions may carry one timezone per row — a timezone column beside a naive timestamp, or an object column of differently-offset timestamps. Normalise to a single instant before converting:

# pip install "pandas>=2.2"
import pandas as pd

def normalise_per_row_tz(frame: pd.DataFrame, value_col: str, tz_col: str,
                         reporting_tz: str = REPORTING_TZ) -> pd.Series:
    """Naive local times plus a timezone name per row -> one reporting-timezone series."""
    parts = []
    for tz_name, group in frame.groupby(tz_col, dropna=False):
        naive = pd.to_datetime(group[value_col], errors="coerce")
        if pd.isna(tz_name):
            parts.append(naive)                                     # unknown zone: leave as given
            continue
        localized = naive.dt.tz_localize(tz_name, ambiguous="NaT", nonexistent="shift_forward")
        parts.append(localized.dt.tz_convert(reporting_tz).dt.tz_localize(None))
    return pd.concat(parts).sort_index()

ambiguous="NaT" marks the hour that occurs twice when clocks go back — a genuinely undecidable value that should surface as missing rather than be guessed, and nonexistent="shift_forward" handles the hour that does not exist in spring. Count the resulting NaT values and report them; in most datasets there are a handful per year, and each one is a real ambiguity in the source data.

Variant Fix 2: Writing UTC Deliberately

Reports that are compared with server logs should stay in UTC — but say so in the file, because a bare 08:14 is otherwise read as local time:

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def write_utc_report(frame: pd.DataFrame, dest: Path, sheet: str = "Events") -> Path:
    out = frame.copy()
    for col in [c for c in out.columns if isinstance(out[c].dtype, pd.DatetimeTZDtype)]:
        out[col] = out[col].dt.tz_convert("UTC").dt.tz_localize(None)
        out = out.rename(columns={col: f"{col}_utc"})
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm:ss") as writer:
        out.to_excel(writer, sheet_name=sheet, index=False, startrow=1)
        book, ws = writer.book, writer.sheets[sheet]
        note = book.add_format({"italic": True, "font_color": "#475569"})
        ws.write(0, 0, "All timestamps are UTC (no daylight saving)", note)   # changed: state the convention
        for i, col in enumerate(out.columns):
            ws.write(1, i, str(col))
    return dest

A one-line note above the header is the cheapest possible defence against a misreading that otherwise surfaces weeks later in a support ticket. Where the workbook feeds another system rather than a person, keep the ISO string column instead — text is unambiguous, and the consuming code parses it explicitly.

Export strategies compared Exporting local time with a named column is clear for readers, loses the offset, and sorts correctly except across a daylight-saving change within the same hour. Exporting UTC with a note is clear for log comparison, loses the local reading, and sorts correctly. An ISO text column is lossless and unambiguous but sorts as text and is awkward for readers. Writing both a local datetime and an ISO instant is lossless and clear at the cost of an extra column. Strategy Clear to readers Lossless Sorts in Excel Local time column yes offset lost yes UTC column + note needs the note local lost yes ISO text column awkward yes as text Local + ISO columns yes yes yes

Reading Such Files Back

A report written in local time is read back as naive timestamps, and code that compares them with UTC data must re-localise:

# pip install "pandas>=2.2"
import pandas as pd

def read_report(path: str, local_cols: tuple[str, ...] = ("created_at_london",),
                reporting_tz: str = REPORTING_TZ) -> pd.DataFrame:
    frame = pd.read_excel(path)
    for col in local_cols:
        if col in frame.columns:
            frame[col] = (pd.to_datetime(frame[col], errors="coerce")
                            .dt.tz_localize(reporting_tz, ambiguous="NaT", nonexistent="shift_forward"))
    for col in [c for c in frame.columns if c.endswith("_utc_iso")]:
        frame[col.replace("_iso", "")] = pd.to_datetime(frame[col], utc=True, errors="coerce")
    return frame

Where both columns exist, prefer the ISO instant: it round-trips exactly, while re-localising local times inherits the same ambiguity problems as the original conversion. This is the practical argument for always exporting the extra column — it makes the file usable by both people and code without either compromising.

Verification

Assert that no aware columns remain, that the conversion preserved the instants, and that the offset applied matches the reporting timezone at each row's date.

# pip install "pandas>=2.2"
import pandas as pd

def verify_excel_ready(original: pd.DataFrame, ready: pd.DataFrame,
                       col: str = "created_at", reporting_tz: str = REPORTING_TZ) -> None:
    aware_left = [c for c in ready.columns if isinstance(ready[c].dtype, pd.DatetimeTZDtype)]
    assert not aware_left, f"timezone-aware columns would still fail to_excel: {aware_left}"
    local_col = f"{col}_{reporting_tz.split('/')[-1].lower()}"
    assert local_col in ready.columns, f"expected renamed column {local_col}"
    recovered = ready[local_col].dt.tz_localize(reporting_tz, ambiguous="NaT", nonexistent="NaT")
    expected = original[col].dt.tz_convert(reporting_tz)
    comparable = recovered.notna() & expected.notna()
    assert (recovered[comparable] == expected[comparable]).all(), "local times do not match the source instants"
    iso_col = f"{col}_utc_iso"
    if iso_col in ready.columns:
        back = pd.to_datetime(ready[iso_col], utc=True)
        assert (back == original[col].dt.tz_convert("UTC")).all(), "ISO instant column does not round-trip"
    ambiguous = int((~comparable & original[col].notna()).sum())
    print(f"verified; {ambiguous} row(s) fall in an ambiguous or non-existent local hour")

Reporting the ambiguous-hour count rather than asserting it is zero is deliberate: a report covering the night clocks change will legitimately contain such rows, and the number belongs in the job's log so nobody is surprised when two rows show the same local time.

FAQ

Can I keep the offset by writing a string? Yes — an ISO 8601 string keeps everything, at the cost of Excel treating it as text. That is the right trade for machine-read files.

Does xlsxwriter support timezones? No engine does; the limitation is the file format, not the library.

What about dates without times? Pure dates have no timezone and never raise. The error only affects datetime64[ns, tz] columns.

Why did my column become timezone-aware in the first place? Database drivers, APIs and pd.to_datetime(..., utc=True) all produce aware columns. That is good for storage and arithmetic — convert only at the export boundary.

Part of Working with Excel Dates and Number Formats.