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-12-10

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.

Key Formulas at a Glance

Timestamp to Date (from Seconds)

excel

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

Date to Timestamp (to Seconds)

excel

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

1. Step-by-Step: Convert Unix Timestamp to Date

Follow these steps to correctly convert a timestamp into a human-readable date.

The Core Formula Explained

The magic formula to convert a Unix timestamp in seconds (e.g., in cell A1) is:

excel

Assuming A1 contains a Unix timestamp in seconds:

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

Here’s why it works:

A1/86400: Divides your timestamp by the number of seconds in a day (24 hours * 60 minutes * 60 seconds = 86400). This calculates how many days have passed since the Unix Epoch.
DATE(1970,1,1): This is Excel's internal serial number for the Unix Epoch start date (January 1, 1970).
The Addition: By adding the number of days to the epoch start date, you get the final Excel serial number for your specific date and time.

Step 1: Enter the Formula

Click on an empty cell (e.g., B1) and type the formula, replacing `A1` with the cell containing your timestamp.

Handling Milliseconds (13-digit timestamp)

If your timestamp is in milliseconds, simply divide it by 1000 first. The formula becomes: =A1/1000/86400 + DATE(1970,1,1)

Step 2: Format the Cell as a Date

After entering the formula, Excel will show a number (e.g., `45353.5`). This is Excel's "serial date". You must format it to see a readable date.

Right-click the cell containing the formula.
Select "Format Cells..." from the context menu.
In the "Number" tab, choose "Date" for a simple date, or "Custom" for more control.
For a date with time, use a custom format like: yyyy-mm-dd hh:mm:ss

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