How to Convert Unix Timestamp to Date in Excel

To convert a Unix timestamp to a date in Excel, enter =A1/86400+DATE(1970,1,1) — with A1 holding your timestamp — then format the result cell as a date. The formula works because Excel counts dates as days since January 1, 1900, and a day holds exactly 86,400 seconds. Or skip the spreadsheet math with ToolNest's free Unix timestamp converter, which converts instantly in your browser.

The 30-second formula

Converting a Unix timestamp to a date in Excel takes three moves. Step 1: paste your timestamps into column A — the familiar 10-digit numbers like 1728288000. Step 2: in B1, enter =A1/86400+DATE(1970,1,1). Step 3: fill the formula down, select column B, and apply a date format (Format Cells > Date, or Ctrl+1). You now have real dates you can sort, filter, and subtract. The arithmetic is doing two things at once: dividing the timestamp by 86,400 turns seconds into days since the Unix epoch, and adding DATE(1970,1,1) shifts Excel's day-zero (January 0, 1900) to the Unix epoch (January 1, 1970). If you'd rather see time too, format as yyyy-mm-dd hh:mm:ss — the fractional part of the serial number is the time of day. This works in Excel, Google Sheets, and LibreOffice alike, because all three share the same serial-date system.

Why the formula works: Excel's serial-date system

Excel does not store dates as text — it stores them as serial numbers, the count of days since its epoch. Day 1 is January 1, 1900 (technically the system counts from January 0, 1900, a quirk that also preserves a nonexistent February 29, 1900 for Lotus 1-2-3 compatibility). So October 1, 2024 is serial 45574: 45,574 days after the start of 1900. A Unix timestamp, meanwhile, is seconds since January 1, 1970 — midnight UTC. Dividing by 86,400 (24 hours × 60 minutes × 60 seconds) converts those seconds into days: 1728288000 / 86400 = 20,003 days since the Unix epoch. Adding DATE(1970,1,1) — serial 25,569, the number of days from 1900 to 1970 — re-bases that count onto Excel's calendar. If the 10-digit number itself is new to you, our guide on what a Unix timestamp is explains the epoch, why APIs love it, and how to read one at a glance. Once the conversion clicks, date arithmetic in Excel opens up — subtracting two converted timestamps gives you days elapsed, and our guide on calculating days between two dates covers that whole toolbox.

Milliseconds, microseconds, and negative timestamps

Not every timestamp is in seconds. JavaScript's Date.now() and many logging pipelines emit millisecond timestamps — 13 digits, like 1728288000000. Feeding one to the seconds formula produces a date in the year 56,000. The fix is one more division: =A1/86400000+DATE(1970,1,1), since a day holds 86,400,000 milliseconds. Some systems go further: microsecond timestamps (16 digits) divide by 86,400,000,000. A reliable sniff test: count the digits — 10 means seconds, 13 means milliseconds, 16 means microseconds. Negative timestamps are dates before 1970, and the same formula handles them: -86400 converts to December 31, 1969. One caveat: Excel cannot display dates before January 1, 1900 as dates, so a timestamp for, say, 1850 will render as a negative serial number — format it as a number and read the offset manually, or convert in a tool instead of Excel.

Time zones: the result is always UTC

A Unix timestamp has no time zone — it is defined as seconds since the epoch at UTC. So the formula's first answer is always the UTC date and time. If your timestamps log server events in UTC but you report in local time, shift the result by your offset: add 5.5/24 for India Standard Time, subtract 5/24 for US Eastern in winter. The /24 converts hours into Excel's day units. Example: =A1/86400+DATE(1970,1,1)-5/24 gives Eastern Standard Time. Two warnings. First, daylight saving: a fixed offset is wrong for half the year in zones that observe DST — for DST-aware conversion you need the timestamp's date to pick the right offset, which usually means a lookup table or a dedicated converter. Second, never 'fix' a time-zone problem by changing the timestamp itself; keep the raw value intact and apply the offset in the formula, so the source data stays canonical.

Formatting the result as a readable date

The formula returns a serial number — 45574.5, say — and Excel only shows it as a date once you format the cell. Select the results, press Ctrl+1, and pick Date or Time; for the full stamp choose Custom and enter yyyy-mm-dd hh:mm:ss. ISO-style formatting (2024-10-01 00:00:00) sorts correctly as text and is unambiguous internationally, which is why most data teams prefer it over regional formats. If you need the date as actual text — for a CSV export or a CONCAT formula — wrap the cell in TEXT(): =TEXT(B1,"yyyy-mm-dd hh:mm:ss") turns the serial into a string. Remember that TEXT output is no longer a date: you can't sort it chronologically beyond what the format's left-to-right order gives you (another reason to use the ISO layout, which sorts correctly). Keep one column as the true date for math and derive display strings from it.

Fixing the three most common errors

Three failures cover nearly every broken conversion. 1. A column of #####. The cell is too narrow, or — more likely — the result is a negative serial: a timestamp before January 1, 1900, which Excel cannot render as a date. Widen the column first; if the hashes persist, your timestamp is pre-1900 or your formula sign is flipped. 2. A big number like 45574 instead of a date. The formula is correct; the cell is formatted as General. Apply a date format and the number resolves into October 1, 2024. 3. #VALUE! errors. Your 'timestamps' are stored as text — common with CSV imports. Convert with =VALUE(A1) first, or select the column, use Data > Text to Columns > Finish, which coerces text to numbers in place. One more subtle trap: timestamps pasted with thousand separators ('1,728,288,000') also read as text — strip the commas before converting.

Skip the formula: convert online

If this is a one-off conversion — a handful of timestamps from an API log, a webhook payload, a database export — the spreadsheet ceremony is overkill. Paste the values into ToolNest's free Unix timestamp converter and get human-readable dates instantly, with millisecond detection and time-zone display handled for you; everything runs in your browser, so log data never leaves your machine. For related timestamp work, see our guide on how to read a JWT token — JWTs carry their expiry (exp) and issued-at (iat) claims as Unix timestamps, so token debugging and timestamp conversion are the same skill.