Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Handle Daylight Saving Time in Excel Timestamps

Excel stores local clock times with no time zone, so the hour that repeats when clocks go back and the hour that never happens when they go forward cause wrong durations, double-counted readings and errors in pandas. Localise naive timestamps safely with ambiguous and nonexistent options, infer the repeated hour from the order of readings, compute true elapsed durations, aggregate by day without 23- and 25-hour surprises, and write results back in a form Excel users understand.
Dates & timesErrors & fixes

A spreadsheet timestamp is a wall-clock reading: 2026-10-25 01:30. It carries no time zone and no hint of whether daylight saving time was in force. Most of the year that does not matter. Twice a year it does. When the clocks go back, the hour from 01:00 to 02:00 happens twice, so a meter that logs every half hour writes 01:30 two times; when they go forward, an hour is skipped, so 01:30 never happens at all — unless someone typed it. Subtracting two such timestamps gives durations an hour out, grouping by day puts 25 or 23 hours into one day without saying so, and pandas refuses to attach a time zone at all, raising an error that tells you to use its ambiguous or nonexistent arguments. This page shows each problem on real clock-change dates for the UK, localises the timestamps safely — inferring the repeated hour from the order of readings — computes true elapsed time, aggregates by day correctly, and writes results back for Excel. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

Bash
pip install pandas openpyxl

The examples use the Europe/London time zone, whose clocks went forward at 01:00 on 29 March 2026 and go back at 02:00 on 25 October 2026; the same code works for any zone in the IANA database. The data is a half-hourly meter export across the October change and a shift log across the March change.

Step 1 — The repeated hour

Python
import pandas as pd

times = pd.date_range("2026-10-24 23:00", periods=10, freq="30min").tolist()
times = times[:6] + [pd.Timestamp("2026-10-25 01:00"), pd.Timestamp("2026-10-25 01:30")] + [pd.Timestamp("2026-10-25 02:00"), pd.Timestamp("2026-10-25 02:30")]
meter = pd.DataFrame({"Reading time": times, "kWh": [1.2, 1.1, 1.0, 0.9, 0.9, 0.8, 0.8, 0.9, 1.0, 1.1]})
print(meter.to_string(index=False))
try:
    meter["Reading time"].dt.tz_localize("Europe/London")
except Exception as e:
    print(type(e).__name__ + ":", e)

The export shows 01:00 and 01:30 twice — the first pair still in British Summer Time, the second after the clocks went back to GMT. As naive timestamps they are simply duplicates, so a pivot by time would add the two readings together and a check for duplicate timestamps would flag perfectly good data. tz_localize refuses to guess which 01:00 is which and raises ValueError: Cannot infer dst time from 2026-10-25 01:00:00, try using the 'ambiguous' argument. The information needed to decide is there, though: the readings are in time order, so the first occurrence must be the summer one.

Step 2 — Localise with ambiguous="infer"

Python
local = meter["Reading time"].dt.tz_localize("Europe/London", ambiguous="infer")
meter["Local"] = local
meter["UTC"] = local.dt.tz_convert("UTC")
meter["Offset"] = local.map(lambda t: t.strftime("%z"))
print(meter[["Reading time", "Offset", "UTC"]].to_string(index=False))
gaps = meter["UTC"].diff().dropna().dt.total_seconds().div(60).unique()
print("minutes between consecutive readings:", gaps)

ambiguous="infer" uses the order of the timestamps to decide: where the wall clock jumps backwards, it assigns the earlier copies to summer time and the later ones to winter time. The offset column shows the switch from +0100 to +0000, and in UTC the readings are exactly thirty minutes apart all the way through — proof that nothing is duplicated and nothing is missing. infer only works on data sorted in time order; for unsorted data, or a single value, pass a boolean array instead (True for daylight time) built from another column, such as a sequence number from the meter.

The night the clocks go back On 25 October 2026 in London the clock reads 00:30, 01:00 and 01:30 in summer time, then goes back from 02:00 to 01:00, so 01:00 and 01:30 occur again in winter time before 02:00. In UTC the readings are evenly spaced thirty minutes apart. 00:30 BST 23:30 UTC 01:00 BST 00:00 UTC 01:30 BST 00:30 UTC 01:00 GMT 01:00 UTC — repeated 01:30 GMT 01:30 UTC — repeated 02:00 GMT 02:00 UTC

Step 3 — The missing hour, and real durations

In spring the opposite happens. A shift log records start and end times on the wall clock; across the change, simple subtraction is an hour out, and a mistyped time inside the gap does not exist:

Python
shifts = pd.DataFrame({"Shift": ["Night A", "Night B", "Early"],
                       "Start": pd.to_datetime(["2026-03-28 22:00", "2026-03-29 00:30", "2026-03-29 01:30"]),
                       "End": pd.to_datetime(["2026-03-29 06:00", "2026-03-29 08:30", "2026-03-29 09:30"])})
shifts["Wall-clock hours"] = (shifts["End"] - shifts["Start"]).dt.total_seconds() / 3600
start = shifts["Start"].dt.tz_localize("Europe/London", nonexistent="NaT")
end = shifts["End"].dt.tz_localize("Europe/London", nonexistent="NaT")
shifts["Actual hours"] = (end - start).dt.total_seconds() / 3600
shifts["Start is valid"] = start.notna()
print(shifts.to_string(index=False))

The night shift from 22:00 to 06:00 looks like eight hours but lasts seven, because the clocks went forward in the middle; paying it as eight, or measuring it against an eight-hour target, is wrong by an hour. Subtracting time-zone-aware timestamps gives the real elapsed time. The early shift claims to start at 01:30 on 29 March, a time that did not exist in London that night. nonexistent="NaT" turns it into a missing value so it is flagged rather than silently moved; the alternatives "shift_forward" and "shift_backward" move it to the edge of the gap, which is right when you know the device clock skipped the hour and wrong when a person mistyped. Durations over 24 hours and shift arithmetic are covered in Calculate Working Hours Between Two Timestamps.

Wall-clock and real durations across the spring change A night shift from 22:00 on 28 March to 06:00 on 29 March 2026 shows eight hours of wall-clock time but lasts seven real hours, because the clocks went forward at 01:00. A start time of 01:30 on 29 March did not exist and is flagged. Naive subtraction 22:00 → 06:00 = 8 h ignores the clock change 01:30 accepted pay and targets wrong Time-zone-aware 22:00 → 06:00 = 7 h true elapsed time 01:30 → NaT, flagged correct for pay and SLAs

Step 4 — Daily totals and writing back to Excel

Grouping by local calendar day is still right — a day is a day to the people reading the report — but the day of a clock change has 23 or 25 hours, which matters for averages and for checks on reading counts:

Python
day = meter.set_index("Local")["kWh"].groupby(lambda t: t.date()).agg(["sum", "count"])
print(day)
print("hours on 25 Oct 2026:", (pd.Timestamp("2026-10-26", tz="Europe/London") - pd.Timestamp("2026-10-25", tz="Europe/London")) / pd.Timedelta(hours=1))

out = meter.assign(**{"Local time": meter["Local"].dt.tz_localize(None), "UTC time": meter["UTC"].dt.tz_localize(None),
                      "Zone": meter["Offset"].map({"+0100": "BST", "+0000": "GMT"})})[["Local time", "Zone", "UTC time", "kWh"]]
out.to_excel("meter_dst.xlsx", index=False)
print(out.to_string(index=False))

Subtracting midnight from the next midnight in the London zone gives 25 hours for 25 October 2026, so a check that expects 48 half-hourly readings per day should expect 50 that day and 46 on the day in March. Excel cannot store time-zone-aware values, so before writing, remove the zone with tz_localize(None) — keeping local time for people and a UTC column for anything that needs to sort, subtract or join reliably — and add a column saying which offset applied. With Zone next to it, the repeated 01:00 is no longer a mystery to someone reading the sheet. Converting between zones more generally is covered in Handle Timezones in Excel Timestamps with Python.

A DST-safe pipeline for Excel timestamps Read the naive timestamps, sort them, attach the local zone with ambiguous infer and nonexistent NaT, flag any missing values, do arithmetic and joins in UTC, and write local time, UTC time and the zone back to Excel as naive columns. Read naive times from Excel Sort + localise ambiguous='infer' Flag gaps nonexistent='NaT' Work in UTC durations, joins Write back local, UTC, zone

Where the clock-change problems hide

Most data never touches the two awkward hours, which is why these bugs survive for years. They tend to appear in three places. Overnight operations — shifts, batch jobs, meters, call centres — have data inside the gap or the repeat every year. Durations and rates computed from naive timestamps are an hour out for anything spanning the change, which shows up as one strangely short or long shift, or a missed service-level target, twice a year. And joins on timestamps between systems where one stores UTC and the other local time drift by an hour for half the year and match for the other half, which is the hardest of all to spot. The defence is the same in each case: attach the zone as soon as the data is read, do arithmetic and joins in UTC, and convert back to local time only for display.

Common pitfalls

  • Treating repeated times as duplicates. On the night the clocks go back they are distinct readings.
  • Naive subtraction across a change. Localise first; real elapsed time differs by an hour.
  • ambiguous="infer" on unsorted data. Sort first, or pass an explicit boolean array.
  • Shifting nonexistent times silently. Flag them unless you know the device skipped the hour.
  • Writing aware timestamps to Excel. Remove the zone and add an offset or zone column.

Conclusion

Excel timestamps are naive wall-clock times, so the repeated autumn hour and the missing spring hour must be resolved when the data is read. Localise with tz_localize(zone, ambiguous="infer") on time-ordered data, flag nonexistent times with nonexistent="NaT", compute durations from aware timestamps or in UTC, remember that clock-change days have 23 or 25 hours, and write local time, UTC time and the zone back to Excel so readers can see what happened.

Frequently asked questions

How do I fix "Cannot infer dst time" when localising Excel timestamps? Sort the timestamps and use tz_localize(zone, ambiguous="infer"), or pass a boolean array that says which ambiguous values are in daylight saving time.

How do I handle times that do not exist because the clocks went forward? Pass nonexistent="NaT" to flag them, or "shift_forward" or "shift_backward" to move them to the edge of the gap when you know the clock skipped the hour.

How do I calculate the real length of a shift across a clock change? Localise both start and end to the time zone and subtract; the result is the true elapsed time, for example seven hours instead of eight.

Can Excel store time zones? No. Store local time and UTC as separate naive columns, with a column for the zone or offset, and convert in Python when reading.

More on dates & times

More on dates & times →