Excel Timestamp to Date: Convert Unix & Serial Numbers
This guide explains how to convert a timestamp to a readable date in Excel. You’ll learn the formulas for both Unix timestamps (seconds since 1970) and Excel serial date numbers (days since 1900 or 1904), along with common pitfalls and formatting tips.
What Is a Timestamp in Excel?
In Excel, a “timestamp” can mean two different things:
- Unix timestamp – an integer counting seconds since January 1, 1970 (UTC). Common in exported data from databases, APIs, and web analytics.
- Excel serial date – a decimal number counting days (and fractions of a day) since January 0, 1900 (Windows) or January 1, 1904 (Mac). This is how Excel internally stores all dates and times.
Knowing which type you have is essential because the conversion formulas are completely different.
How Excel Stores Dates and Times
Excel treats dates as numbers. The integer part represents the day, and the fractional part represents the time of day. For example, the serial number 45000.5 corresponds to noon on a specific day. The base date depends on your system:
- Windows (1900 system): Serial 1 = January 1, 1900. (There’s a known quirk: Excel treats 1900 as a leap year, so serial 60 is the fictitious February 29, 1900.)
- Mac (1904 system): Serial 1 = January 2, 1904. This was the default on older Macs; you can change it in Excel options.
Because of this, if you open a file created on a Mac with the 1904 system, dates will appear shifted by 4 years unless you adjust for the system difference.
Converting a Unix Timestamp to a Date
Unix timestamps are seconds since January 1, 1970, at 00:00 UTC. To convert to an Excel date, divide by the number of seconds in a day (86,400) and then add the Excel serial number for January 1, 1970.
For Windows Excel, January 1, 1970 is serial number 25569. So the formula is:
=A1/86400 + 25569
If your timestamp is in milliseconds (common in JavaScript), divide by 86,400,000 first:
=A1/86400000 + 25569
After entering the formula, format the cell as a date (e.g., yyyy-mm-dd hh:mm:ss) to see the result. If you’re using a Mac with the 1904 date system, replace 25569 with 24107 (the serial for January 1, 1970 in the 1904 system).
Converting an Excel Serial Number to a Date
If your data already contains an Excel serial number (like 45000), you don’t need a formula—just change the cell format to a date. Select the cells, press Ctrl+1 (Windows) or Cmd+1 (Mac), go to the Number tab, choose Date, and pick a format.
If you need to extract the date part (e.g., to remove time), use the INT function:
=INT(A1)
This returns the serial number with the fractional part (time) removed. Format as a date.
Common Mistakes and How to Avoid Them
- Mixing up Unix seconds and milliseconds: Always check the magnitude. A Unix timestamp in seconds is around 1.7 billion; in milliseconds it’s about 1.7 trillion. If you see a 13-digit number, it’s milliseconds.
- Forgetting the 1904 date system: If your dates appear off by 4 years, your workbook likely uses the 1904 system. Check via File → Options → Advanced → “Use 1904 date system”.
- Not formatting the result as a date: The formula returns a serial number; if you don’t format it, you’ll see a large decimal, not a date.
- Ignoring time zones: Unix timestamps are UTC. If you need local time, add or subtract the time zone offset (e.g.,
+ (5/24)for UTC-5).
Formatting the Result as a Readable Date
After conversion, apply a date format. Right-click the cell → Format Cells → Date, or use a custom format code like yyyy-mm-dd hh:mm:ss. You can also use the TEXT function to display the date as text:
=TEXT(A1/86400 + 25569, "yyyy-mm-dd hh:mm:ss")
Note that TEXT returns a text string, not a true date, so you can’t use it in further date calculations. For that, keep the numeric value and just change the display format.
Frequently Asked Questions
How do I convert a Unix timestamp to date in Excel without a formula?
You can’t do it without a formula because Excel doesn’t recognize Unix timestamps natively. You need to use the division and addition formula shown above, then format the cell as a date.
What if my timestamp is in milliseconds?
Divide by 86,400,000 instead of 86,400. For example, =A1/86400000 + 25569. The rest is the same.
Why is my date showing as #####?
This usually means the column is too narrow to display the full date. Widen the column, or change the format to a shorter date (e.g., mm/dd/yyyy).
How do I convert a date to a Unix timestamp in Excel?
Use the reverse formula: =(A1 - 25569) * 86400, where A1 contains a date serial number. Format the result as a number (not a date).
Does the 1904 date system affect conversion?
Yes. If your workbook uses the 1904 system, replace 25569 with 24107 in the Unix conversion formula. Otherwise, the date will be off by 1,462 days (about 4 years).
Can I convert a timestamp stored as text?
Yes, but you may need to use the VALUE function first to turn the text into a number. For example, =VALUE(A1) if A1 contains a numeric timestamp as text. Then apply the conversion formula.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.