Convert Unix Timestamps to Excel Dates with pandas

A CSV from a web analytics tool, a JSON response from a payments API, a database dump of event logs: all of them tend to store time as a Unix timestamp, the number of seconds (or milliseconds) since 1 January 1970 UTC. Pasted into Excel, 1790920800 is just a large number; a report needs 2 Oct 2026 06:00. The conversion is one pandas call — once you know the unit, which the data rarely tells you, and once you have decided which time zone the readers expect, because Unix time is UTC and Excel has no concept of time zones. This page covers detection, conversion, time zones, messy values and the reverse trip. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

events.xlsx has six events from three sources. The API sends seconds, the mobile app milliseconds, and the database export contains a 0 (a placeholder for "unknown" that would otherwise become 1 January 1970) and the text not set. All values arrived as text, which is common for IDs and timestamps in exports.

Step 1 — Detect the unit from the magnitude

import pandas as pd

ev = pd.read_excel("events.xlsx", dtype={"Timestamp": str})
raw = pd.to_numeric(ev["Timestamp"], errors="coerce")

def epoch_unit(value):
    if pd.isna(value) or value <= 0:
        return None
    digits = len(str(int(value)))
    return {10: "s", 13: "ms", 16: "us", 19: "ns"}.get(digits)

ev["Unit"] = raw.map(epoch_unit)
print(ev.assign(Numeric=raw).to_string(index=False))

Present-day timestamps have 10 digits in seconds (1790920800), 13 in milliseconds, 16 in microseconds and 19 in nanoseconds — a rule that holds from September 2001 until the year 2286. That makes the length of the number a reliable guide for current data. Zero and text get no unit, which marks them for special handling instead of letting them turn into 1970. When one source sends a consistent unit, set it explicitly from documentation and use detection only as a check.

Step 2 — Convert to UTC datetimes

pd.to_datetime takes the unit; with mixed units, convert each group with its own:

parts = [pd.to_datetime(raw[idx], unit=unit, utc=True)
         for unit, idx in ev.groupby("Unit").groups.items()]
ev["UTC"] = pd.concat(parts).reindex(ev.index)
print(ev[["EventID", "Timestamp", "Unit", "UTC"]].to_string(index=False))

Each unit group is converted separately and the pieces are put back in the original row order with reindex; rows without a unit get NaT. utc=True marks the results as UTC, which is what Unix time is. The seconds value 1790920800 and the milliseconds value 1790920800000 both become 2 October 2026 06:00 UTC — the same instant from two sources. The zero and the text stay NaT. Without utc=True pandas returns naive datetimes that silently assume UTC, and the next step would be easy to forget.

Step 3 — Convert to the readers' time zone, then drop the zone

Excel stores no time zone, so pandas refuses to write zone-aware datetimes. Convert to the zone the readers think in, then remove the zone information:

ev["Local (London)"] = ev["UTC"].dt.tz_convert("Europe/London").dt.tz_localize(None)
ev["Local (New York)"] = ev["UTC"].dt.tz_convert("America/New_York").dt.tz_localize(None)
print(ev[["EventID", "UTC", "Local (London)", "Local (New York)"]].dropna().to_string(index=False))

06:00 UTC on 2 October is 07:00 in London (British Summer Time) and 02:00 in New York. tz_localize(None) keeps the local clock time and discards the zone, which is exactly what Excel can store. Converting first matters: dropping the zone from the UTC values would leave UTC clock times that readers take for local time. Label the column with the zone so nobody has to guess. The error you get if you skip this step is covered in Fix "Excel does not support datetimes with timezones" in pandas.

Step 4 — Write to Excel with a readable format

out = ev[["EventID", "Source", "Timestamp", "Local (London)"]].copy()
out["Note"] = ev["Unit"].isna().map({True: "no valid timestamp", False: ""})
with pd.ExcelWriter("events_converted.xlsx", engine="xlsxwriter", datetime_format="dd mmm yyyy hh:mm:ss") as xw:
    out.to_excel(xw, sheet_name="Events", index=False)
    xw.sheets["Events"].set_column("D:D", 22)
from openpyxl import load_workbook
cell = load_workbook("events_converted.xlsx")["Events"]["D2"]
print(cell.value, "|", cell.number_format)

The cell holds a real Excel date-time, so it sorts and filters as a date.

Step 5 — The same conversion as an Excel formula

When the conversion must live in the workbook — a sheet that people paste new exports into — the formula divides seconds by the seconds in a day and adds the Excel serial of 1 January 1970:

import subprocess
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Seconds", "Milliseconds", "From seconds", "From ms", "London (BST, +1h)"])
ws.append([1790920800, 1790920800000, "=A2/86400+DATE(1970,1,1)", "=B2/86400000+DATE(1970,1,1)",
           "=C2+1/24"])
for col in "CDE":
    ws[f"{col}2"].number_format = "dd mmm yyyy hh:mm"
wb.save("epoch_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "epoch_formulas.xlsx"],
               check=True, capture_output=True)
row = [c.value for c in load_workbook("recalc/epoch_formulas.xlsx", data_only=True).active[2]]
print(row)

Both formulas give 2 October 2026 06:00, matching pandas. Time zones in a formula are only a fixed offset — the +1/24 adds one hour for BST — and the offset changes with daylight saving, which a formula cannot know. That is the strongest argument for converting in Python, where the zone database handles every change.

Step 6 — Convert back to Unix time

APIs often want timestamps in requests, so the reverse is common too:

local = pd.Series(pd.to_datetime(["2026-10-02 07:00", "2026-12-24 18:30"]))
as_utc = local.dt.tz_localize("Europe/London").dt.tz_convert("UTC")
seconds = (as_utc - pd.Timestamp("1970-01-01", tz="UTC")) // pd.Timedelta(seconds=1)
print(pd.DataFrame({"Local": local, "UTC": as_utc, "Unix seconds": seconds}).to_string(index=False))

Localise the naive local times to their zone first, convert to UTC, then count whole seconds since the epoch. Subtracting the epoch and floor-dividing by one second avoids depending on the internal resolution of the datetime column, which in pandas 3 is not always nanoseconds. 07:00 in London on 2 October gives back 1790920800. The Christmas Eve time shows the zone database at work: in December London is on GMT, so 18:30 local is 18:30 UTC, while the October time was an hour ahead of UTC. A fixed offset applied to both would have got one of them wrong.

Performance notes

pd.to_datetime with a unit is vectorised and converts millions of timestamps per second; time zone conversion is similarly fast because it works on whole arrays with the zone database. The per-unit grouping adds one conversion per unit, not per row. The expensive mistake is converting row by row with datetime.fromtimestamp in a loop or apply, which is easily a hundred times slower and, worse, uses the machine's local time zone unless told otherwise — so the same script gives different answers on a laptop in London and a server in UTC.

Common pitfalls

• Guessing the unit. A milliseconds value read as seconds lands tens of thousands of years in the future; detect or document the unit.
• Zero placeholders. 0 becomes 1 January 1970; treat it as missing.
• Forgetting the time zone. Unix time is UTC; convert to local time before dropping the zone.
• Numbers read as floats. Large timestamps read as float64 can lose precision at the microsecond level; read them as text or integers.
• Fixed offsets in formulas. +1/24 is right only half the year in the UK.
• Local midnight boundaries. An event at 03:59 UTC on 3 October belongs to 2 October in New York; aggregate by day after converting to the readers' zone, not before.

Conclusion

Unix timestamps count seconds (or milliseconds, microseconds) since 1970 in UTC. Detect the unit from the digit count, convert with pd.to_datetime(values, unit=..., utc=True), convert to the readers' zone and drop the zone with tz_localize(None) before writing to Excel, treat zeros and text as missing, and keep the original value alongside. Use =A2/86400+DATE(1970,1,1) only when the conversion must stay in the sheet, accepting that it cannot follow daylight saving.

Frequently asked questions

How do I convert a Unix timestamp to a date in pandas? Use pd.to_datetime(series, unit="s", utc=True) for seconds or unit="ms" for milliseconds. The result is a UTC datetime that you can convert to a local time zone.

How can I tell whether a timestamp is in seconds or milliseconds? For dates between 2001 and 2286, timestamps in seconds have 10 digits, milliseconds 13, microseconds 16 and nanoseconds 19, so the length of the number identifies the unit.

How do I write the converted timestamps to Excel? Convert the UTC datetimes to the readers' zone with dt.tz_convert, remove the zone with dt.tz_localize(None), and write them with to_excel; Excel cannot store time-zone-aware values.

What is the Excel formula for a Unix timestamp? =A2/86400+DATE(1970,1,1) for seconds, or =A2/86400000+DATE(1970,1,1) for milliseconds, formatted as a date-time. The result is in UTC unless you add a fixed offset.

Related

• Up one level: Working with Dates and Times in Excel Data — dates and times in spreadsheets.
• Handle Timezones in Excel Timestamps with Python — zone conversion in depth.
• Fetch API Data into Excel with Python Requests — where epoch timestamps usually come from.
• Fix Excel Serial Numbers Showing Instead of Dates — Excel's own number-based dates.