Excel Epoch to Date Formulas
=A1/86400+DATE(1970,1,1) Tested on: Excel for Microsoft 365 and Google Sheets · last tested
To convert epoch to date in Excel, divide the Unix timestamp by 86,400 (the seconds in a day) and add the Excel date for 1 January 1970. This guide covers seconds, milliseconds, the reverse conversion, time zones and Google Sheets. It belongs to the Unix timestamp guides; to check single values, convert epoch time on the homepage.
Excel Formula to Convert a Unix Timestamp to a Date
The Unix timestamp Excel formula is =A1/86400+DATE(1970,1,1). Excel stores dates as days since 1900, while Unix time counts seconds since 1970. Dividing by 86,400 turns seconds into days, and DATE(1970,1,1) adds the gap between the two starting points. Format the result as a date.
- Enter the formula. With the Unix timestamp in A1, enter =A1/86400+DATE(1970,1,1) in B1.
- Format the cell. Format B1 as a date and time, for example the custom format yyyy-mm-dd hh:mm:ss.
- Fill down. Drag the formula down the column to convert every timestamp. Use 86400000 instead of 86400 for milliseconds.
| A1 (Unix seconds) | =A1/86400+DATE(1970,1,1) | Formatted |
|---|---|---|
| 0 | 25569 | 1970-01-01 00:00:00 |
| 1000000000 | 37143.07407 | 2001-09-09 01:46:40 |
| 1735689600 | 45658 | 2025-01-01 00:00:00 |
The middle column is what you see before formatting: DATE(1970,1,1) is serial number 25569 in the 1900 date system. If your result looks like 45658, the formula worked and only the cell format is missing. To keep the date as text in one step, wrap it: =TEXT(A1/86400+DATE(1970,1,1),"yyyy-mm-dd hh:mm:ss").
The formula also works in workbooks that use the 1904 date system, because DATE() always returns the right serial for the workbook it is in. A hard-coded 25569 does not, so prefer DATE(1970,1,1).
Convert Millisecond Timestamps in Excel
A 13-digit value is in milliseconds. Divide by 86,400,000 (milliseconds in a day) instead:
=A1/86400000+DATE(1970,1,1) To show the milliseconds, use the custom format yyyy-mm-dd hh:mm:ss.000. Excel keeps about 15 significant digits, which is enough for millisecond precision on present-day dates but not for microseconds or nanoseconds. For 16- or 19-digit values, divide by 86,400,000,000 or 86,400,000,000,000, and expect the sub-millisecond part to be rounded.
Imported timestamps sometimes arrive as text, often with stray spaces or a leading apostrophe. Plain numeric text works in the formula, but anything with extra characters returns #VALUE!. Clean it inside the formula with =VALUE(TRIM(A1))/86400+DATE(1970,1,1), or use Data → Text to Columns to turn the whole column into numbers once. After that, the plain formula works on every row again.
If a column mixes units, check the length first: =IF(LEN(A1)>=13, A1/86400000, A1/86400)+DATE(1970,1,1). When you are not sure what a value is, paste it into the timestamp to date converter to see its unit.
Convert an Excel Date to a Unix Timestamp
For Excel date to Unix timestamp conversion, reverse the formula: subtract the epoch and multiply by 86,400.
=(A1-DATE(1970,1,1))*86400
=ROUND((A1-DATE(1970,1,1))*86400, 0) Format the result as a number with no decimals, otherwise Excel may show it as a date. The ROUND version removes tiny floating-point remainders that appear when times were typed with seconds. Multiply by 86,400,000 instead for milliseconds.
For the current time, use NOW(), but remember it returns your computer's local time, not UTC. The next sections show how to correct for that.
Google Sheets Unix Time Formulas
The same formulas work for Google Sheets Unix time conversion. Sheets also has a dedicated function, EPOCHTODATE, with a unit argument:
=EPOCHTODATE(A1) // seconds (the default unit)
=EPOCHTODATE(A1, 2) // milliseconds
=EPOCHTODATE(A1, 3) // microseconds Per Google's documentation, the result of EPOCHTODATE is in UTC, not the spreadsheet's time zone, and negative timestamps are not accepted. For dates before 1970, use =A1/86400+DATE(1970,1,1), which handles negative values. For the reverse in Sheets, =(A1-DATE(1970,1,1))*86400 works as in Excel.
Adjust Excel Epoch Dates for Your Time Zone
Unix time is UTC, so every formula above returns a UTC date. To show a local time, add the UTC offset as a fraction of a day: hours divided by 24.
=A1/86400+DATE(1970,1,1)+5/24 // UTC+5, e.g. Pakistan
=A1/86400+DATE(1970,1,1)-5/24 // UTC−5, New York in winter
=A1/86400+DATE(1970,1,1)+5.5/24 // UTC+5:30, India Pitfall: Excel has no time zone database, so it does not know about daylight saving time. A fixed offset is wrong for half the year in zones like New York or London. For mixed seasons, convert in Python or SQL, or put the offset for each row in its own column. You can check any single value against the UTC to local time converter, or count days into the year with the day of the year tool.
The reverse needs the opposite sign. A local time typed in a cell on a machine set to UTC+5 becomes Unix time with =(A1-5/24-DATE(1970,1,1))*86400. Database exports often store Unix seconds already; the MySQL Unix timestamp guide shows how to convert them before export instead.
Excel Epoch to Date FAQ
How do I get the current timestamp in Excel?
Use =(NOW()-DATE(1970,1,1))*86400 and format the cell as a number. NOW() returns your computer's local time, so the result is off by your UTC offset. Subtract the offset in hours divided by 24 from NOW() to get true Unix time, for example =(NOW()-5/24-DATE(1970,1,1))*86400 for a machine set to UTC+5.
Why does my Excel epoch formula show a number instead of a date?
The formula returns an Excel date serial, such as 45658 for 1 January 2025, and the cell still has a number format. Select the cell, open Format Cells and pick a date or a custom format like yyyy-mm-dd hh:mm:ss. The value is correct; only the display changes.
Why is my converted date thousands of years in the future?
The timestamp is in milliseconds but the formula divides by 86,400 as if it were seconds. Check the digit count: 13 digits means milliseconds, so divide by 86,400,000 instead. Excel cannot display dates after 31 December 9999, so a value that far out shows as a row of # signs.
Does the Excel epoch formula work in Google Sheets?
Yes. =A1/86400+DATE(1970,1,1) works the same way in Google Sheets. Sheets also has a built-in function, =EPOCHTODATE(A1, 1), where the second argument is the unit: 1 for seconds, 2 for milliseconds and 3 for microseconds. Format the result as a date and time to read it.