How do I handle timezones in Excel for Unix time?

You must manually add or subtract the offset as a fraction of a day. For example, for UTC+8, add 8/24 to your date formula. This does not account for DST.

Why does my Excel formula show a number like 45000 instead of a date?

You need to format the cell containing the formula. Right-click the cell, choose "Format Cells," and select a "Date" or "Custom" format like yyyy-mm-dd hh:mm:ss.

The Complete Guide to Unix Timestamps in Excel

Master the formulas and techniques to accurately convert Unix epoch time to human-readable dates and back within Microsoft Excel.

Last updated: 2025-10-23

What is a Unix Timestamp?

A Unix timestamp is the total number of seconds that have elapsed since 00:00:00 UTC on 1 January 1970. Excel, however, calculates dates based on the number of days since 1 January 1900. This guide provides the formulas to bridge that gap.

1. Convert Unix Timestamp to Date

Formulas to turn a numeric timestamp into a readable date format.

From Seconds (10-digit timestamp)

This is the most common scenario. The formula divides the timestamp by the number of seconds in a day (86400) and adds the result to Excel's epoch start date.

excel

Assuming A1 contains a Unix timestamp in seconds:

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

From Milliseconds (13-digit timestamp)

If your timestamp is in milliseconds, you first need to convert it to seconds by dividing by 1000.

excel

Assuming A1 contains a Unix timestamp in milliseconds:

=A1/1000/86400 + DATE(1970,1,1)

Formatting is Key!

After applying the formula, the cell will show a number. You MUST format this cell as a Date or a custom format like yyyy-mm-dd hh:mm:ss to see the actual date and time.

2. Convert Date to Unix Timestamp

Formulas to convert an Excel date into a numeric Unix timestamp.

To Seconds (10-digit timestamp)

This formula calculates the difference between your date and the Unix epoch start date, then converts the resulting number of days into seconds.

excel

Assuming A1 contains a valid Excel date/time:

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

To Milliseconds (13-digit timestamp)

To get millisecond precision, simply multiply the seconds-based result by 1000.

excel

Assuming A1 contains a valid Excel date/time:

=(A1 - DATE(1970,1,1)) * 86400 * 1000

3. Handling Timezones

Since Unix timestamps are in UTC, you may need to adjust for local timezones.

Excel's date system is not timezone-aware. To adjust for a specific timezone, you must manually add or subtract the offset. The offset is represented as a fraction of a day (e.g., 8 hours is 8/24).

excel

To add 8 hours for a UTC+8 timezone:

= (A1/86400 + DATE(1970,1,1)) + 8/24

Beware of Daylight Saving Time (DST)

Manually adding a fixed offset does not account for DST. If your data spans periods with DST changes, this simple formula can lead to off-by-one-hour errors. For precise, DST-aware conversions, a programmatic solution or a dedicated tool is often better.

4. Troubleshooting Common Issues

Fixes for frequent errors and unexpected results.

#VALUE! Error: This usually means the source cell (e.g., A1) is not a valid number. Ensure it doesn't contain text or special characters. Use Excel's VALUE() function to convert text-formatted numbers.
Wrong Date (e.g., a date in the 1900s): You likely forgot to format the result cell as a Date. The raw number is Excel's serial date, not the final formatted date.
Off by Several Years: Double-check if your timestamp is in seconds (10 digits) or milliseconds (13 digits) and use the correct formula. Using the seconds formula for a milliseconds timestamp will result in a date far in the future.
Formula shows as text: Make sure the cell's format is set to 'General' before you type the formula, not 'Text'.
Comma vs. Semicolon: Depending on your Windows regional settings, you might need to use a semicolon (;) instead of a comma (,) as the separator in the `DATE` function. E.g., `DATE(1970;1;1)`.

FAQ

Q: What is the formula to convert Unix timestamp to date in Excel?
A: For seconds in cell A1, use =A1/86400 + DATE(1970,1,1). For milliseconds, use =A1/1000/86400 + DATE(1970,1,1). Then, format the cell as a date.

Q: How do I handle timezones in Excel?
A: Manually add or subtract the offset. For UTC+8, add 8/24 to the formula. Be aware this is a fixed offset and won't adjust for Daylight Saving Time.

Q: Why does my formula just show a big number?
A: You need to format the result cell. Right-click > Format Cells > Date.

Back to Main Converter