Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Handle Timezones in Excel Timestamps with Python

Excel cannot store a timezone. Convert aware datetimes to one zone, strip the offset for writing, label the sheet, and re-localise correctly when reading the file back.

Excel has no timezone. A cell holds a day count and nothing else, so the instant 2026-08-15 18:00+02:00 and the instant 2026-08-15 18:00Z are indistinguishable once written. openpyxl handles this honestly — it raises rather than silently dropping the offset — which means every script that writes timestamps has to make a decision. This guide covers the three-step pattern that keeps those timestamps unambiguous, the daylight-saving edges that break naive code, and how to read the values back into aware datetimes. It expands on the timezone section of Working with Dates and Times in Excel Data.

Convert, strip, label — writing timestamps Excel can hold Three stages. An aware timestamp of six in the evening UTC is first converted to the target zone, becoming eight in the evening Berlin time while representing the same instant. The offset is then removed with tz_localize None, leaving a naive value Excel can store. Finally the zone name is written into the sheet as a header so the reader knows what the bare timestamp means. Skipping the third stage is what makes reports unreproducible. 1 · convert 2026-08-15 18:00+00:00 ↓ 2026-08-15 20:00+02:00 same instant 2 · strip tz_localize(None) ↓ 2026-08-15 20:00 naive — Excel can store it 3 · label "All times Europe/Berlin" written into the sheet without this the file is unreproducible later Excel stores a day count. The zone lives in the sheet, or it lives nowhere.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

Python 3.9 and later ship zoneinfo in the standard library, so no third-party timezone package is needed:

Python
from zoneinfo import ZoneInfo
print(ZoneInfo("Europe/Berlin"))

On a bare Linux container without the system tz database, install the fallback: pip install tzdata.

Step 1 — See the failure clearly

openpyxl refuses aware datetimes outright:

Python
from datetime import datetime, timezone
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = datetime(2026, 8, 15, 18, 0, tzinfo=timezone.utc)
# ValueError: Excel does not support timezones in datetimes.
# The tzinfo in the datetime/time object must be set to None.

pandas is quieter and therefore more dangerous. to_excel on a tz-aware column will raise on some engine and dtype combinations and silently write the naive local wall-clock time on others — so never rely on the default. Make the conversion explicit:

Python
import pandas as pd

events = pd.DataFrame({
    "event": ["login", "export", "logout"],
    "at": pd.to_datetime([
        "2026-08-15T16:04:00Z",
        "2026-08-15T17:30:00Z",
        "2026-08-15T19:15:00Z",
    ], utc=True),
})
print(events["at"].dtype)      # datetime64[ns, UTC]

Step 2 — Convert, strip, label

The whole pattern is three lines of transformation and one line of documentation:

Python
import pandas as pd

REPORT_ZONE = "Europe/Berlin"

# 1. Convert — same instants, expressed in the readers' zone.
events["at"] = events["at"].dt.tz_convert(REPORT_ZONE)

# 2. Strip — drop the offset so Excel can hold the value.
events["at"] = events["at"].dt.tz_localize(None)

# 3. Label — write the zone where a reader will see it.
with pd.ExcelWriter("events.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd hh:mm") as writer:
    events.to_excel(writer, sheet_name="Events", index=False, startrow=1)

    book, sheet = writer.book, writer.sheets["Events"]
    note = book.add_format({"italic": True, "font_color": "#5b6780"})
    sheet.write(0, 0, f"All times {REPORT_ZONE}", note)
    sheet.set_column("B:B", 19)

tz_convert and tz_localize are easy to confuse and do opposite things:

MethodRequiresDoes
tz_localize("Europe/Berlin")naive inputAsserts these wall-clock times are Berlin times
tz_convert("Europe/Berlin")aware inputRe-expresses the same instant in Berlin
tz_localize(None)aware inputDiscards the offset, keeping the wall clock

Calling tz_localize on already-aware data raises; calling tz_convert on naive data raises. The error messages are clear, but the conceptual mistake — localising when you meant to convert — silently shifts every timestamp by the offset when you get it the other way round.

Step 3 — Survive daylight saving

Twice a year, local time is not a function of itself. In the spring one hour does not exist; in the autumn one hour happens twice. Any code that builds aware timestamps from local strings meets this eventually.

The two hours where local time is not a function Two timelines. On the spring transition the clock jumps from one fifty-nine to three a.m., so any local time in the two o'clock hour is nonexistent and localising it raises unless a policy is given. On the autumn transition the two o'clock hour repeats, first at plus two and then at plus one, so a local time in that hour is ambiguous and maps to two distinct instants. local wall-clock time across a transition spring 01:00 – 01:59 02:00 – 02:59 never happens 03:00 – 03:59 nonexistent= "shift_forward" autumn 01:00 – 01:59 02:00 +02 02:00 +01 03:00 – 03:59 ambiguous= True or False the reliable fix: store UTC upstream and only ever tz_convert converting from an instant is total; localising a wall clock is not
Python
import pandas as pd

local = pd.to_datetime(["2026-10-25 02:30", "2026-03-29 02:30"])

# Default: raises on both the ambiguous and the nonexistent value.
try:
    local.tz_localize("Europe/Berlin")
except Exception as exc:
    print(type(exc).__name__, exc)

# State the policy explicitly instead.
resolved = local.tz_localize(
    "Europe/Berlin",
    ambiguous=False,               # autumn repeat: take the second (winter) pass
    nonexistent="shift_forward",   # spring gap: move to the first valid instant
)
print(resolved)

Choosing ambiguous=False versus True is a business decision, not a technical one — it decides whether a 02:30 event on transition night is recorded before or after the clocks go back. Passing ambiguous="NaT" is the honest option when you genuinely cannot tell, because it marks the rows rather than guessing.

All of this disappears if timestamps arrive as UTC instants. Conversion from an instant is always well defined; localisation of a wall clock is not. Where you control the upstream — a database extract, an API — capture UTC and convert only for display. See exporting SQL query results to Excel for pushing that decision into the query.

Step 4 — Read the timestamps back

The full round trip, and where the zone information lives A UTC instant is converted to the report zone and stripped of its offset to be written into the workbook, where only a naive wall-clock time is stored. On the way back, that naive value is re-localised using the zone recorded in the sheet's header, then converted to UTC so it can be joined with other systems. The header label is the only thing carrying the zone across the boundary; without it, the return leg is guesswork. UTC instant 16:04+00:00 convert + strip 18:04, naive in the workbook cell value: 18:04 · no offset anywhere header: "All times Europe/Berlin" tz_localize(zone from header) then tz_convert("UTC") UTC again

A naive timestamp read from Excel is meaningless until you re-apply the zone the file was written in. If step 3 put the zone in the sheet, the round trip is exact:

Python
import pandas as pd

# The label written at row 0; the table starts at row 1.
header = pd.read_excel("events.xlsx", sheet_name="Events", nrows=0, header=None)
label = str(header.iloc[0, 0]) if not header.empty else ""
zone = label.replace("All times", "").strip() or "UTC"

df = pd.read_excel("events.xlsx", sheet_name="Events", skiprows=1)

# Re-localise into the documented zone, then normalise to UTC for joining.
df["at"] = (
    df["at"]
    .dt.tz_localize(zone, ambiguous=False, nonexistent="shift_forward")
    .dt.tz_convert("UTC")
)
print(df["at"].dtype)      # datetime64[ns, UTC]

When rows genuinely originate in different zones, a label cannot describe them. Carry the zone per row instead:

Python
import pandas as pd

df = pd.DataFrame({
    "site": ["berlin", "denver", "singapore"],
    "local_time": pd.to_datetime(["2026-08-15 20:00", "2026-08-15 12:00",
                                  "2026-08-16 02:00"]),
    "zone": ["Europe/Berlin", "America/Denver", "Asia/Singapore"],
})

# groupby keeps each zone's rows together so tz_localize is vectorised per group.
df["utc"] = (
    df.groupby("zone", group_keys=False)
      .apply(lambda g: g["local_time"].dt.tz_localize(g.name).dt.tz_convert("UTC"))
)
print(df[["site", "local_time", "zone", "utc"]])

That two-column shape — a readable local time plus the IANA zone name — is the most robust thing you can put in a spreadsheet. It is readable by a human, and it reconstructs the exact instant for a machine.

Common pitfalls and fixes

SymptomCauseFix
ValueError: Excel does not support timezonesWriting an aware datetimetz_localize(None) after converting.
Every timestamp shifted by the offsettz_localize used where tz_convert was meantConvert aware data; localise naive data.
AmbiguousTimeErrorAutumn transition hourPass ambiguous= explicitly, or "NaT" to mark them.
NonExistentTimeErrorSpring transition gapPass nonexistent="shift_forward".
Nobody can tell what zone the file usesLabel step skippedWrite the zone into a header cell or a metadata sheet.
ZoneInfoNotFoundError in a containerNo system tz databasepip install tzdata.
Times drift by seconds over a round tripBinary fraction of a dayRound to the second after reading.
Rows from different offices compared wronglyOne zone assumed for allCarry a per-row zone column.

Performance and scale notes

Timezone conversion is vectorised and cheap — tz_convert on a million-row column is a single offset computation per distinct offset, not per row. The expensive operations are the ones that fall back to Python objects.

The groupby plus apply pattern above is the main one to watch: it runs once per distinct zone, which is fine for a handful of offices and slow for thousands of rows with high zone cardinality. When cardinality is high, map through the small set of distinct zones instead:

Python
import pandas as pd

out = pd.Series(pd.NaT, index=df.index, dtype="datetime64[ns, UTC]")
for zone, idx in df.groupby("zone").groups.items():
    out.loc[idx] = (
        df.loc[idx, "local_time"]
          .dt.tz_localize(zone, ambiguous=False, nonexistent="shift_forward")
          .dt.tz_convert("UTC")
    )
df["utc"] = out

Two other habits keep large jobs fast. Convert once, at the boundary, rather than inside every function that touches the column — repeated tz_convert calls allocate a new array each time. And avoid .dt.tz_localize inside an apply over rows; it constructs a fresh timezone object per call, which is roughly two orders of magnitude slower than the column-level operation.

For workbooks large enough that memory matters, do the timezone work chunk by chunk as you read, using the streaming approach in reading large Excel files in chunks with pandas. Timezone conversion is stateless per row, so it parallelises across chunks with no coordination.

Conclusion

Excel cannot hold a timezone, so your script has to. Convert aware timestamps to the one zone your readers think in, strip the offset with tz_localize(None) so openpyxl will accept them, and write the zone name into the sheet where a human will find it six months later. Keep upstream data in UTC wherever you can, because converting from an instant is always well defined while localising a wall clock is not. And when rows really do come from different places, carry the IANA zone name in its own column — that is the one representation that survives every round trip.

Frequently asked questions

Why does openpyxl raise ValueError on a timezone-aware datetime? Because the Excel file format has no field for an offset. Rather than silently discarding information, openpyxl refuses the write and makes you decide which zone the value represents.

Should I store UTC or local time in the spreadsheet? Store what the readers will reason about. Operational reports read by one office should carry that office's local time; anything joined with other systems or spanning regions should carry UTC. Whichever you pick, say so in the sheet.

How do I record the zone so the file is self-describing? Put it in a header cell above the table, in the sheet name, or in a dedicated metadata sheet. A separate column holding the IANA zone name works when rows genuinely come from different zones.

What happens on the night the clocks change? Local times become ambiguous or non-existent for one hour. Convert from UTC rather than localising local strings, and where you must localise, pass the ambiguous and nonexistent arguments explicitly instead of accepting the default.

Can I keep the UTC offset in a separate column? Yes, and it is a good pattern. Write the local timestamp for reading plus a text column holding the IANA zone name, so the original instant can be reconstructed exactly.