Epoch Time Converter Excel Formula

To convert epoch time (Unix timestamp) to a readable date in Excel, use the formula =((A1/60)/60)/24+DATE(1970,1,1) for seconds, or =(A1/86400)+DATE(1970,1,1) for a more direct approach. This article explains the formulas, how to handle milliseconds, timezone adjustments, and common pitfalls.

Formula

The core formula is: converted_date = (epoch_seconds / 86400) + DATE(1970,1,1).

• epoch_seconds: the Unix timestamp (integer) – number of seconds since 1970-01-01 00:00:00 UTC.
• 86400: number of seconds in a day (24 × 60 × 60).
• DATE(1970,1,1): Excel’s serial number for January 1, 1970 (which is 25569 in Excel’s date system).

The result is an Excel date-time serial number, which you then format as a date or date-time.

Worked Example

Using the default value from the calculator: epoch time = 1,609,459,200 (which is 2021-01-01 00:00:00 UTC).

1. Divide by 86400: 1,609,459,200 ÷ 86,400 = 18,624.0
2. Add the Excel serial for 1970-01-01: 18,624 + 25,569 = 44,193
3. Format cell as yyyy-mm-dd hh:mm:ss → you get 2021-01-01 00:00:00 (UTC).

If your Excel is set to a timezone other than UTC, you may see a different time – see the timezone section below.

Handling Milliseconds and Different Units

Unix timestamps can be in seconds (10 digits), milliseconds (13 digits), or even microseconds (16 digits). The formula changes accordingly:

• Seconds: =A1/86400 + DATE(1970,1,1)
• Milliseconds: =A1/86400000 + DATE(1970,1,1)
• Microseconds: =A1/86400000000 + DATE(1970,1,1)

A quick way to check: if the number has 13 digits, it’s milliseconds; if 10 digits, seconds. If you’re unsure, divide by 1000 and see if the result looks like a plausible date.

Time Zones and Local Time

The formula above returns the date in UTC. To display local time, add the timezone offset as a fraction of a day. For example, for Eastern Standard Time (UTC-5), use:

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

For UTC+2, use + 2/24. If your Excel already has a timezone setting, you can also use =A1/86400 + DATE(1970,1,1) + (TIMEZONE_OFFSET/24). Remember that the offset may change with daylight saving time – you may need to adjust manually or use a dynamic formula.

Common Mistakes and Pitfalls

• Forgetting to format the cell: The result is a number; you must apply a date format (e.g., yyyy-mm-dd hh:mm:ss) to see it as a date.
• Using the wrong unit: Mixing seconds and milliseconds gives wildly wrong dates.
• Ignoring timezone: The raw formula gives UTC; if you expect local time, add the offset.
• Excel’s date system: On Windows, Excel’s date serial starts at 1900-01-01; on Mac (pre-2016) it was 1904. The formula DATE(1970,1,1) works correctly in both because it uses the actual date, not a hardcoded number.
• Negative timestamps: For dates before 1970, the formula still works, but ensure the cell format can display negative serial numbers (it will show a date, but Excel may display as ##### if the column is too narrow).

Converting Dates Back to Epoch Time

To go the other way, use: =(A1 - DATE(1970,1,1)) * 86400 for seconds, or * 86400000 for milliseconds. This is useful when you need to send dates to systems that expect Unix timestamps.

Frequently Asked Questions

Why does my formula show a number like 44193 instead of a date?
That’s because the cell is formatted as General. Select the cell, press Ctrl+1 (or Cmd+1 on Mac), choose Date or Custom, and enter a format like yyyy-mm-dd hh:mm:ss.

How do I convert epoch time in milliseconds?
Divide by 86,400,000 instead of 86,400. For example, =A1/86400000 + DATE(1970,1,1).

Does this formula work for dates before 1970?
Yes, it works for negative timestamps, but you must ensure the cell format can display the date. Some Excel versions may show ##### if the column is too narrow – widen it.

How do I handle timezone differences in Excel?
Add or subtract the offset as a fraction of a day. For example, for UTC+5:30, add 5.5/24. Remember to adjust for daylight saving time if applicable.

Can I use this formula in Google Sheets?
Yes, the same formula works in Google Sheets, but note that Google Sheets uses a different date serial (1899-12-30) – however, DATE(1970,1,1) still returns the correct serial, so the formula works identically.

Why is my converted date off by one day?
Check if your timestamp is in milliseconds but you used the seconds formula, or vice versa. Also verify your timezone offset – adding 24 hours when you meant to subtract will shift the date.

Related

• JavaScript Calculator Code: How to Build One
• JS Calculator Code: How to Build One
• King Code Calculator: What It Is & How to Use

in t | Epoch time (seconds) | sec | 1609459200 | 0
out dt | Converted date (UTC) | date | (t / 86400) + DATE(1970,1,1) | 2
out local | Local date (UTC+2) | date | (t / 86400) + DATE(1970,1,1) + (2/24) | 2