ThisCalculator

Everyday

Unix Timestamp to Excel Date: Easy Conversion

Updated · Standard formula, shown below

Unix Timestamp to Excel Date: Easy Conversion

To convert a Unix timestamp to an Excel date, divide the timestamp by 86400 and add 25569, then format the cell as a date. For example, the formula =A1/86400+25569 converts a Unix timestamp in cell A1 to an Excel date. This works because Excel dates count days since January 0, 1900, while Unix timestamps count seconds since January 1, 1970.

Background: Why the Numbers Differ

Unix timestamps and Excel dates use different starting points and different units, which is why a direct conversion isn’t obvious. A Unix timestamp is the number of seconds that have elapsed since 00:00:00 UTC on January 1, 1970 (the Unix epoch). Excel, on the other hand, stores dates as serial numbers, where day 1 corresponds to January 1, 1900 (with a quirk: Excel treats February 29, 1900 as a valid date, even though 1900 was not a leap year).

The offset between the two systems is 25,569 days. That’s the number of days from January 0, 1900 (Excel’s day 0) to January 1, 1970 (Unix’s day 0). Since a Unix timestamp is in seconds, dividing by 86,400 (the number of seconds in a day) converts it to days, and adding 25,569 aligns the two calendars.

How the Conversion Works

To convert a Unix timestamp to an Excel date, you need to perform two operations: convert seconds to days and shift the epoch. Here’s the breakdown:

  • Seconds to days: Divide the timestamp by 86,400 (24 hours × 60 minutes × 60 seconds).
  • Epoch shift: Add 25,569 to the resulting number of days.

The formula in Excel is: =A1/86400 + 25569 (assuming the timestamp is in cell A1). After entering the formula, format the cell as a date (e.g., yyyy-mm-dd or dd/mm/yyyy) to see the human-readable date.

Worked Example

Let’s convert the Unix timestamp 1,600,000,000 (which corresponds to September 13, 2020, at 12:53:20 UTC) to an Excel date.

  1. Divide by 86,400: 1,600,000,000 ÷ 86,400 = 18,518.518518… days.
  2. Add 25,569: 18,518.518518 + 25,569 = 44,087.518518.
  3. The integer part (44,087) is the date serial number. In Excel, serial 44,087 corresponds to September 13, 2020.
  4. The fractional part (0.518518) represents the time: 0.518518 × 24 hours = 12.444 hours, which is about 12:26 PM. (The actual time is 12:53:20, but rounding in the fraction causes a slight difference.)

So the formula =1600000000/86400+25569 returns 44087.518518, which displays as 2020-09-13 12:26 if formatted with time. To get the exact time, you’d need to account for the seconds precisely—the formula does that automatically; the example’s rounding is only for illustration.

How to Choose the Right Method

There are several ways to convert Unix timestamps in Excel, and the best one depends on your situation:

  • If the timestamp is in a cell: Use the formula =A1/86400+25569 and format as a date.
  • If you need to convert many timestamps: Apply the same formula down a column, then copy and paste as values to replace the original numbers.
  • If you want to avoid formulas: Use Excel’s Text to Columns wizard (Data tab) with a custom format, but the formula method is simpler and more reliable.
  • If you need the date and time separately: Use =INT(A1/86400+25569) for the date and =MOD(A1/86400+25569,1) for the time.

Common Problems and Mistakes

Here are the most frequent issues people encounter when converting Unix timestamps:

  • Forgetting the epoch offset: Dividing by 86,400 alone gives a date in 1970, not the correct date. Always add 25,569.
  • Formatting as a number: If you see a large number like 44087, the cell is formatted as a number, not a date. Change the cell format to a date style.
  • Milliseconds: Some Unix timestamps are in milliseconds (e.g., 1600000000000). If so, divide by 86,400,000 first, then add 25,569.
  • Time zone issues: Unix timestamps are UTC. If you need local time, add or subtract your time zone offset (in hours divided by 24) to the formula.
  • Negative timestamps: For dates before 1970, the timestamp is negative. The formula still works, but Excel may display a series of # symbols if the date is before 1900 (which Excel doesn’t support).

Comparing with Other Spreadsheet Software

The conversion method is similar in other spreadsheet programs, but the epoch offset may differ. For example, Google Sheets uses the same 1900 date system as Excel, so the same formula works. LibreOffice Calc also uses the same system, but it handles the 1900 leap year bug differently (it doesn’t include the phantom February 29, 1900), so the offset is 25,569 for dates after March 1, 1900, but for earlier dates it’s 25,568. Since Unix timestamps start in 1970, the difference rarely matters.

If you’re working with a database or a programming language, you might use built-in functions. For instance, in Python, you can use datetime.fromtimestamp(), and in JavaScript, new Date(timestamp*1000). But in Excel, the formula is the standard approach.

Alternatives to Manual Conversion

If you don’t want to write formulas, there are a few alternatives:

  • Paste Special: You can paste Unix timestamps into Excel, then use the Text to Columns wizard with a custom format, but it’s not as straightforward.
  • Power Query: In newer versions of Excel, you can use Power Query to transform a column of timestamps by adding a custom column with the formula #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Timestamp]).
  • Online converters: Many websites offer free Unix timestamp converters, but they’re not practical for large datasets.

Frequently Asked Questions

Why does Excel show ##### after conversion?

This usually means the column is too narrow to display the date. Increase the column width or change the date format to a shorter one (e.g., mm/dd/yyyy). If the date is before 1900, Excel cannot display it as a date, so you’ll see ##### permanently.

How do I convert a Unix timestamp with milliseconds?

Divide the timestamp by 86,400,000 (since there are 1,000 milliseconds in a second) and then add 25,569. For example, if the timestamp is 1600000000000, use =A1/86400000+25569.

What if my timestamp is in a text format?

If the timestamp is stored as text, Excel may not recognize it as a number. Use the VALUE function to convert it: =VALUE(A1)/86400+25569. Alternatively, use the Text to Columns wizard to convert text to numbers.

How do I convert an Excel date back to a Unix timestamp?

Reverse the formula: =(A1-25569)*86400. This gives the number of seconds since January 1, 1970. If you need milliseconds, multiply by 1000.

Does the conversion work for dates after 2038?

Yes, in Excel, the formula works for any date after 1970, including far into the future. The 2038 problem affects 32-bit Unix systems, but Excel uses 64-bit numbers, so it’s not an issue.

Why is my converted time off by a few hours?

Unix timestamps are in UTC. If your Excel is set to a different time zone, the displayed time will be off by the time zone offset. Add or subtract the offset (in days) to the formula. For example, for Eastern Time (UTC-5), add -5/24.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.