How to convert a Unix timestamp to a date in Excel

The formula and why it works

A Unix timestamp counts seconds since midnight UTC on January 1, 1970. Excel counts days since January 1, 1900, and stores the time of day as a fraction. So the conversion has two steps: divide by 86,400 to turn seconds into days, then add the serial number of January 1, 1970, which DATE(1970,1,1) gives you.

The sheet is an order export from a web shop. Order SO-50812 has created_at 1793784600. The formula returns 46330.395833: day 46330 is November 4, 2026 and .395833 of a day is 9:30 AM.

Excel may show the raw number or only the date, depending on the format the cell picks up. Select the column, press Ctrl+1, choose Custom and type yyyy-mm-dd hh:mm:ss to see the time as well.

Millisecond timestamps (13 digits)

JavaScript, many APIs and most logging tools record milliseconds, not seconds. Those timestamps have 13 digits instead of 10, and there are 86,400,000 milliseconds in a day.

Divide one by 86,400 by mistake and you get a date in the year 58812, which Excel cannot display, so the cell fills with #####. That is the giveaway that you have milliseconds.

The seconds format rounds away the milliseconds (09:31:02.417 shows as 09:31:02). To see them, use the custom format yyyy-mm-dd hh:mm:ss.000 on the cell.

A column with both: check the length

Exports that merge two systems sometimes mix seconds and milliseconds in one column, like D here. LEN works on numbers too: it returns 10 for a seconds timestamp and 13 for milliseconds, so let IF pick the divisor.

This holds for any date between 2001 and 2286, which covers real data. If a value has some other length, it is not a Unix timestamp in seconds or milliseconds (microseconds have 16 digits, and Excel only keeps 15 significant digits, so ask for a different export).

Convert to your time zone with an offset cell

Unix time is always UTC. To show local time, put the offset in hours in its own cell (G2 holds -5 for New York in winter) and add it divided by 24, because Excel counts in days. Use $G$2 so the reference stays put when you fill down.

SO-50814 was created at 23:48:30 UTC on November 3. In New York that is 18:48:30 the same day. The date can change too: a late-evening UTC time can be the next morning in Asia. Half-hour zones work the same way: type 5.5 for India.

Excel has no time zone rules, so daylight saving is up to you. Change G2 when the clocks change, or add an offset column next to the dates if the data spans a switch. Keep the UTC column as the source of truth and convert from it.

Back the other way: Excel date to Unix timestamp

Reverse the steps: subtract January 1, 1970, multiply by 86,400, and remove your offset first if the date is local time. G5 holds the local start of a sale, November 27, 2026 at 9:00 AM in New York.

Wrap the result in ROUND. Excel stores times as binary fractions, and multiplying back up can leave a tiny decimal that breaks an exact match in an API call or a lookup. Multiply by 86,400,000 instead if the other system expects milliseconds.

NOW() returns your computer's local time, not UTC, so =(NOW()-DATE(1970,1,1))*86400 is off by your offset. Subtract $G$2/24 as well.

ISO 8601 text like 2026-11-04T09:30:00Z

Many APIs send dates as text in ISO format: date, a T, the time, and Z for UTC. Excel does not read that whole string as a date, so DATEVALUE on it fails. Cut it into parts with LEFT and MID and rebuild it with DATE and TIME, which works in every version and every regional setting.

If the text ends with an offset such as +02:00 instead of Z, the time shown is local to that offset. Subtract the offset (2/24) to get UTC. A shorter alternative is =DATEVALUE(LEFT(E2,10))+TIMEVALUE(MID(E2,12,8)). To turn the result into a Unix timestamp, use the formula in the previous section with an offset of 0.