Convert a Unix Timestamp to a Date in Excel

To convert a Unix timestamp to a date in Excel, divide it by 86,400 and add the date 1 January 1970: =A2/86400+DATE(1970,1,1). Then format the cell as a date and time. This guide is for anyone who exports data from an app, database or web API and gets columns of long numbers like 1718454600 instead of dates.

What is a Unix timestamp?#

A Unix timestamp is the number of seconds since midnight UTC on 1 January 1970. That moment is called the Unix epoch. So 0 means 1970-01-01 00:00:00, and 1718454600 means 15 June 2024 at 12:30 UTC. Many systems store time this way because one number is easy to sort and compare.

Excel stores time differently. An Excel date is a serial number: the count of days since 1 January 1900, with the time of day as a decimal. So 45458 is 15 June 2024, and 45458.5 is noon on that day. The guide on why Excel dates are numbers explains this system in more detail.

To convert one to the other you need two steps: change seconds into days, then move the starting point from 1900 to 1970.

The Unix timestamp formula in Excel#

The formula divides by the seconds in one day (24 × 60 × 60 = 86,400) and adds the 1970 start date:

=A2/86400+DATE(1970,1,1)

Here is a small export with timestamps in column B:

In C2, type =B2/86400+DATE(1970,1,1) and fill it down (Windows: Ctrl+D; Mac: ⌘D). Each part does one job:

• B2/86400 turns 1718454600 seconds into about 19,889.52 days.
• DATE(1970,1,1) returns 25569, the serial number of the Unix epoch.
• Adding them gives 45458.52, which is 15 June 2024 at 12:30.

You may see the shorter =B2/86400+25569. It gives the same answer in the default date system, but DATE(1970,1,1) is easier to read and stays correct in a workbook that uses the 1904 date system, which some older Mac files do.

How to show the date and time, not a number#

The formula returns a serial number, so at first you may see 45458.52. That is normal. You just need a date format:

1. Select the result cells.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. Choose Custom and type yyyy-mm-dd hh:mm, or pick a Date format.

If you need the result as text, for example to join it into a sentence, wrap it in TEXT:

=TEXT(B3/86400+DATE(1970,1,1),"yyyy-mm-dd hh:mm")

This returns the text 2024-06-15 12:30. Text can't be sorted or used in date maths, so keep a real date column too. The guide to changing the date format covers more format codes, and why a date shows as a number helps when a column still looks wrong. If you only want the day, see how to remove the time from a date: =INT(B3/86400)+DATE(1970,1,1) returns 15 June 2024 with no time.

Timestamps in milliseconds#

A 13-digit timestamp is in milliseconds, not seconds. JavaScript and many web tools use this form, for example 1718454600000. Divide by 86,400,000 (the milliseconds in a day) instead:

=A2/86400000+DATE(1970,1,1)

A quick way to tell them apart: a 10-digit number is seconds and a 13-digit number is milliseconds, for any date between 2001 and 2286. If your result is a date far in the future, or shows only ##### signs, you have used the wrong divisor.

Some systems also export microseconds (16 digits). The idea is the same: divide by 86,400,000,000. Negative timestamps are dates before 1970, and the formula handles them too, as long as the result falls on or after 1 January 1900, the earliest date Excel can show in its default system.

How to adjust for your time zone#

Unix time is always UTC, so the converted date is UTC too. To show local time, add or subtract the offset in hours divided by 24:

Excel doesn't know about daylight saving time. A fixed offset is fine for one season, but if your data spans a clock change, the hour will be off for part of the year. Many teams keep reports in UTC for this reason.

When the timestamp is stored as text#

Exports from CSV files or APIs sometimes bring timestamps in as text. Text usually sits on the left of the cell, and the formula may return #VALUE!. Wrap the cell in VALUE to turn it into a number first:

=VALUE(A2)/86400+DATE(1970,1,1)

The VALUE function guide explains when Excel converts text to numbers by itself and when it needs help. If the text is a normal date like "15/06/2024" rather than a timestamp, use the guide to converting text to dates instead.

How to convert an Excel date to a Unix timestamp#

To go the other way, subtract the 1970 start date and multiply by 86,400:

=(B2-DATE(1970,1,1))*86400

If B2 holds 15 June 2024 12:30, the result is 1718454600. Make sure B2 is in UTC first, or subtract your offset (for example -8/24 for Singapore) inside the brackets. Format the result as a number with no decimals, or Excel may show it in scientific notation like 1.72E+09.

Practise it#

The whole trick rests on one idea: Excel dates are day counts. Practise how Excel stores dates in a live spreadsheet, with DATE, YEAR, MONTH and DAY checked instantly as you type. Then try the TEXT and VALUE lesson to turn numbers into readable text and back. The exercise on this page lets you convert a real timestamp first.