Unix timestamp to Excel date: 3 formulas that work
Excel counts days from 1900 and Unix counts seconds from 1970, so the whole conversion is one division and one constant. Below: the formula for seconds, the millisecond variant, the reverse direction, and the timezone trap that makes right answers look wrong.
Why the formula works
Excel stores dates as serial numbers: 1 is January 1, 1900, and each whole number adds one day. Unix timestamps are seconds counted from January 1, 1970, which sits at serial number 25,569 in Excel's calendar. Dividing a timestamp by 86,400 turns seconds into days; adding that to the serial of 1970-01-01 gives the Excel serial of the moment. That is the entire math behind all three formulas below.
Formula 1: Unix seconds to an Excel date
- Put the timestamp in
A1: a 10-digit number such as1727740800(seconds since the epoch) - In the result cell enter
=(A1/86400)+DATE(1970,1,1) - Format the cell as a date and time (Ctrl+1, then Date). Without that step Excel shows another big serial number
The result is the UTC reading of the timestamp. If it looks off by a few hours, that is the timezone trap covered below, not a broken formula.
Formula 2: epoch milliseconds to an Excel date
The most common follow-up mistake. A 13-digit timestamp like 1727740800000 is in milliseconds, and running it through the seconds formula lands you roughly 66,000 years in the future. The millisecond variant is:
=(A1/86400000)+DATE(1970,1,1)
Quick tell: 10 digits means seconds, 13 digits means milliseconds. A converted date somewhere in the year 66,000 means milliseconds went through the seconds formula.
Formula 3: an Excel date back to Unix seconds
Reverse the arithmetic: =(A1-DATE(1970,1,1))*86400, where A1 holds the date, with or without a time of day. A cell that carries a time of day produces fractional seconds; wrap the result in ROUND(...,0) if you need a clean integer. For milliseconds, multiply by 86,400,000 instead of 86,400.
The timezone trap
A Unix timestamp is UTC by definition. An Excel serial carries no timezone at all; it is just a number. The formulas above therefore always produce the UTC reading of the timestamp, and the cell shows that same reading no matter where the spreadsheet is opened. If you expected local time, the gap is exactly your UTC offset: in UTC-5, a timestamp for 14:00 UTC displays as 9:00.
The UTC-safe pattern is to add the offset deliberately and visibly: =(A1/86400)+DATE(1970,1,1)+(5/24) for UTC+5, or -(5/24) for UTC-5. Daylight saving makes the offset move through the year, which is why server-side timestamps should stay UTC end to end. And if the values themselves were written down in local time, no formula can repair them; fix the writer instead.
Sanity-check the result
Convert once, then cross-check: paste the same timestamp into the Unix Timestamp Converter. It restates the epoch in seconds and milliseconds and shows UTC and your local time side by side, so a spreadsheet that disagrees is telling you which timezone assumption it made.
Frequently Asked Questions
Why is my converted date off by a few hours?
Timezone, almost every time. The formula returns the UTC reading of the timestamp, and the number of hours it differs from your expectation is your local UTC offset. Correct for it deliberately by adding the offset in days to the result, for example +(5/24) for UTC+5, and check the value against a converter that shows UTC and local time side by side.
How do I convert epoch milliseconds in Excel?
Divide by 86,400,000 instead of 86,400, since a day holds 86.4 million milliseconds: =(A1/86400000)+DATE(1970,1,1), then format the cell as a date and time. Thirteen-digit timestamps are milliseconds; ten-digit ones are seconds. A result in the year 66,000 or so means milliseconds went through the seconds formula.