Excel Convert Epoch to Datetime: The Exact Formula & Timezone Guide

To make excel convert epoch to datetime, divide the timestamp by 86,400 (the number of seconds in a day) and add Excel’s base date offset. The exact formula is =(A1/86400)+DATE(1970,1,1). After entering the formula, apply Custom Cell Formatting using mm/dd/yyyy hh:mm:ss to view the human-readable calendar date and time.

The Core Formula: Dividing by 86400 and Adding DATE(1970,1,1)

Excel and Unix systems track time differently. Excel counts continuous days starting from January 1, 1900, while the Unix epoch counts continuous seconds starting from January 1, 1970. Based on data from Exceljet, a standard 24-hour day contains exactly 86,400 seconds.

To translate between the two systems, you divide the Unix timestamp by 86,400 to turn those seconds into days. Then, you account for the gap between the two starting years. As noted by Microsoft Q&A, this gap is exactly 25,569 days. You can write this offset in your formula as either the raw number 25569 or the DATE(1970,1,1) function.

Put your epoch timestamp in cell A1 and enter =(A1/86400)+DATE(1970,1,1) in cell B1 to calculate the raw decimal value.

How to Apply Custom Cell Formatting

Your formula will initially return a decimal number like 44538.66. To make this readable, select the cell, press Ctrl + 1 to open the Format Cells dialog box, and click the “Custom” category. Type mm/dd/yyyy hh:mm:ss into the “Type” field and hit OK to reveal the actual date and time string.

The Digit Count Guide: Handling 13-Digit Millisecond Timestamps

Standard Unix timestamps are 10 digits long and measure seconds. However, lots of API exports and telemetry logs use 13-digit millisecond timestamps for higher precision. If you use the standard formula on these, you’ll end up with an incorrect date thousands of years in the future.

According to Excel Insider, you need to adjust your divisor to 86,400,000 for 13-digit data. This divides the milliseconds into seconds and the seconds into days all in one mathematical step.

How Do I Apply Timezone Adjustments to Converted Epoch Dates?

Unix epoch time is always recorded in Coordinated Universal Time (UTC). Your converted formula will output the UTC datetime by default.

To get your local time, add or subtract the hour difference as a fraction of a 24-hour day directly in your formula. Just attach +(hours/24) or -(hours/24) to the end of the core conversion math.

Quick Reference Cheat Sheet for Major Time Zones

Here are the static adjustments for common global time zones. Keep in mind you’ll need to manually update these formulas when Daylight Saving Time (DST) shifts occur.

Extracting the Calendar Date Only Using INT and TEXT Functions

If you only need to group metrics by the calendar date—not track the exact hour and minute—wrap your formula in the INT function. The formula =INT(A1/86400)+DATE(1970,1,1) strips away the time decimals, leaving you with a clean whole number representing midnight of that date.

If you’re exporting this data to a CSV or combining it with other text cells, use the TEXT function instead. The formula =TEXT((A1/86400)+DATE(1970,1,1), "mm/dd/yyyy") converts the math straight into a static text string that won’t break if someone accidentally changes the spreadsheet formatting.

Bulk Processing Millions of Rows with Power Query

Applying standard cell formulas to massive datasets will severely lag your spreadsheet. For millions of rows, handle the conversion in Power Query during the data ingestion phase instead.

Open the Power Query Editor and add a Custom Column. Use the M code snippet #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [EpochColumn]) for 10-digit timestamps. This does the math efficiently in the background without bloating your file size, giving you a clean datetime column ready for PivotTable analysis.

Troubleshooting the Negative Dates Error (####)

Seeing a row of hash symbols (####) filling your cells usually means one of two things. First, try double-clicking the boundary of the column header to widen it—the full mm/dd/yyyy hh:mm:ss format requires a lot of horizontal space.

If the cell still shows hash symbols after widening, your formula is generating a negative date. Excel’s date system cannot display dates before January 1, 1900. Check your source data: make sure you aren’t using the 10-digit formula on a 13-digit timestamp, and verify that your timezone subtraction didn’t push an early-1970 date backward into 1899.

FAQ

Why does my Excel cell show ####### after applying the epoch conversion formula?

Usually, your column is simply too narrow for the full ‘mm/dd/yyyy hh:mm:ss’ format. Try widening it. If it still shows hashes, your formula resulted in a negative number, meaning the date falls before Excel’s minimum cutoff of January 1, 1900.

How do I convert a 13-digit millisecond epoch timestamp to a date in Excel?

Because 13-digit timestamps track milliseconds, you need to increase your formula’s divisor by a factor of 1,000. Use the updated formula =(A1/86400000)+DATE(1970,1,1) to handle both the millisecond-to-second and second-to-day conversions accurately.

How can I adjust my converted Excel datetime for my specific local timezone (e.g., EST, AEST)?

Since epoch time is measured in UTC, you adjust it by adding or subtracting the hour difference as a fraction of a 24-hour day. To switch to EST (UTC-5), just add -(5/24) to the very end of your conversion formula.

What is the difference between Excel’s date system and the Unix epoch?

Excel counts continuous days starting from January 1, 1900. The Unix Epoch counts continuous seconds starting from January 1, 1970. The number 25,569 bridges this gap, representing the exact number of days between those two starting points.

Conclusion

Getting readable dates from epoch timestamps comes down to matching Unix seconds to Excel’s daily clock using the 86400 divisor and the DATE(1970,1,1) offset. Just remember to check whether your data is in 10-digit seconds or 13-digit milliseconds before you apply the formula to ensure the math lines up.

Start by pasting =(A1/86400)+DATE(1970,1,1) into your worksheet, tweak it for your local timezone, and apply custom formatting to actually see the date.

Would you like me to help you write a custom VBA macro to automate this conversion across multiple workbooks?