Excel stores dates as serial numbers counting from January 1, 1900, while Unix epoch time counts seconds from January 1, 1970. To convert a Unix timestamp (in seconds) to a readable Excel datetime, use the formula =(A1/86400)+DATE(1970,1,1), then format the result cell as a date or datetime. That single formula bridges the two systems and is the fastest way to turn raw timestamps in Excel into human-friendly dates.
Content Table
Why Excel and Unix count time differently
Both systems store dates as plain numbers, but they start counting from different points and use different units.
- Excel date serial number: Day 1 is January 1, 1900. Each whole number is one day, and the decimal part is the fraction of a day. So 45000.5 means noon on a specific date in 2023.
- Unix (epoch) time: Second 0 is January 1, 1970 at 00:00:00 UTC. Each unit is one second, so a typical timestamp like 1700000000 is a big number in the billions.
The gap between the two starting points matters. There are 25,569 days between January 1, 1900 and January 1, 1970 in Excel's default 1900 date system. That number shows up in a lot of conversion formulas. If you want the deeper background on where the 1970 anchor comes from, our guide to epoch time and Unix timestamps covers it.
DATE(1970,1,1)
instead of a hardcoded number.
Convert epoch time to datetime in Excel
Say your Unix timestamp in seconds sits in cell A1. Here are two equivalent formulas.
=(A1/86400)+DATE(1970,1,1)
=(A1/86400)+25569Both do the same thing:
-
A1/86400converts seconds into days (there are 86,400 seconds in a day). -
Adding
DATE(1970,1,1)(or its serial equivalent, 25569) shifts the result onto Excel's 1900-based calendar.
The formula returns a raw serial number at first. To see an actual date, select the cell, press
Ctrl+1
, choose
Custom
, and enter a format like
yyyy-mm-dd hh:mm:ss
. Now the cell shows a full readable datetime.
DATE(1970,1,1)
version is safer than hardcoding 25569 because it reads clearly and won't break if you paste it into a workbook using a different base date.
Handling milliseconds and other units
Not every timestamp is in seconds. JavaScript, many APIs, and JSON payloads often use milliseconds, which is a 13-digit number instead of 10. Adjust the divisor accordingly.
| Unit | Divisor | Formula |
|---|---|---|
| Seconds (10 digits) | 86400 |
=(A1/86400)+DATE(1970,1,1)
|
| Milliseconds (13 digits) | 86400000 |
=(A1/86400000)+DATE(1970,1,1)
|
| Microseconds (16 digits) | 86400000000 |
=(A1/86400000000)+DATE(1970,1,1)
|
A quick way to tell which unit you have: count the digits. A 10-digit number in the range starting with 1 is almost always seconds for any recent date. If it's 13 digits, it's milliseconds. If you're unsure which precision your data uses, our breakdown of seconds vs milliseconds vs microseconds explains how to spot the difference.
Convert Excel date to Unix timestamp
Going the other direction is just as common. If you need to convert an Excel date to a Unix timestamp (for example, to load values into a database or API), reverse the math.
=(A1-DATE(1970,1,1))*86400
This subtracts the 1970 anchor from your Excel date serial number, then multiplies the day difference back into seconds. Wrap it in
INT()
if you want a clean whole-second value with no fractional remainder:
=INT((A1-DATE(1970,1,1))*86400)Storing timestamps this way keeps your data compact and timezone-neutral. If you're moving these values into a data store, our notes on Unix timestamps in databases cover the storage and query trade-offs.
Dealing with time zones
Unix timestamps are always in UTC by definition. Excel has no concept of time zones, so the formulas above give you the UTC datetime, not your local time. To shift to a local zone, add or subtract the offset as a fraction of a day.
=(A1/86400)+DATE(1970,1,1)+(-5/24)
The
-5/24
subtracts 5 hours, converting UTC to US Eastern Standard Time. Change the number to match your offset. Keep two things in mind:
- This is a fixed offset. It does not handle daylight saving time automatically, so a January and a July timestamp may need different offsets.
- If you only care about the exact instant and not the wall-clock display, leave it in UTC to avoid confusion. Read more on why UTC is the safe default in our explainer on Unix timestamps and UTC.
Using DATEVALUE for text timestamps
Sometimes your data isn't a Unix number at all. It's a date stored as text, like
"2024-03-15", which Excel refuses to treat as a real date. That's where the
DATEVALUE
function comes in. The DATEVALUE formula in Excel parses a text string into an Excel date serial number.
=DATEVALUE("2024-03-15")
This returns 45366, the serial number for that date, which you can then format as a date or feed into the reverse formula to get a Unix timestamp. DATEVALUE only handles the date portion. For text that includes a time, pair it with
TIMEVALUE:
=DATEVALUE("2024-03-15")+TIMEVALUE("14:30:00")
DATEVALUE respects your system's regional date settings, so
"03/15/2024"
may fail on a machine set to day-first formatting. ISO 8601 style (
yyyy-mm-dd
) is the most reliable input.
Common problems and fixes
| Symptom | Cause | Fix |
|---|---|---|
| Result shows a huge number | Cell is still formatted as General/Number | Format the cell as Date or Custom datetime |
| Date is off by exactly 4 years | Workbook uses the 1904 date system (common on old Mac files) | Add 1462 to your day count, or switch the workbook's date system in options |
| Time is wrong by a few hours | Timezone offset not applied | Add or subtract the offset as hours/24 |
| Wildly wrong date (year 55000+) | Timestamp is in milliseconds but divided as seconds | Divide by 86400000 instead of 86400 |
| #VALUE! error with DATEVALUE | Text format doesn't match regional settings | Use yyyy-mm-dd format or check your locale |
Excel's default 1900 system can't represent dates before January 1, 1900, so negative Unix timestamps (dates before 1970) will produce errors or nonsense in older versions. If you work with historical dates, see how negative Unix timestamps handle pre-1970 dates.
Convert whole columns of timestamps at once
Skip the Excel formula juggling. Paste a list of epoch times and our batch converter turns every Unix timestamp into a readable datetime in one go, seconds or milliseconds, no formatting headaches.
Try the batch converter →
The formula returns a correct Excel date serial number, but the cell is still formatted as a plain number. Select the cell, press Ctrl+1, and apply a Date or Custom format like yyyy-mm-dd hh:mm:ss. The same number will then display as a readable datetime.
Count the digits. A recent timestamp in seconds has 10 digits, while milliseconds has 13. If your converted date lands thousands of years in the future, you likely divided a millisecond value by 86400 instead of 86400000. Adjust the divisor to fix it.
It's the number of days between Excel's start date (January 1, 1900) and the Unix epoch (January 1, 1970) in the 1900 date system. Adding it shifts a Unix-based day count onto Excel's calendar. Using DATE(1970,1,1) instead is clearer and less error-prone.
No. Excel has no timezone awareness, and Unix timestamps are defined in UTC. The basic formula gives you the UTC datetime. To show local time, add or subtract your offset as hours divided by 24, but remember this fixed offset won't adjust for daylight saving time.
Your workbook is probably using the 1904 date system, which older Mac Excel files default to. Either switch it in Excel's options under Advanced, or add 1462 to your day count in the formula to compensate for the different base date.