Home → Unix Timestamp in Excel
Unix Timestamp in Excel
Excel does not store Unix timestamps as its native date serial format, but simple formulas can translate between an epoch number and an Excel date serial.
Working example
The key conversion is dividing seconds by 86,400 because there are that many seconds in a day. For milliseconds, divide by 86,400,000. Timezone handling must be added separately if the Unix value represents UTC and the worksheet is intended to show a specific local zone.
Unit and timezone checks
In Excel, the key context is different from code libraries: Excel stores calendar values as serial days, while Unix data counts from the 1970 epoch. The conversion formula must account for both the offset and the day-to-second scale.
- Confirm whether the API expects seconds or milliseconds.
- When a spreadsheet cell represents a local date and time, establish the intended timezone before exporting its value as Unix time.
- Keep UTC as the reference when comparing distributed logs.
- Do not infer the unit solely from digit count.
Common mistakes in Excel
- Passing milliseconds where an API expects seconds.
- Formatting a local time as if it were UTC.
- Dropping fractional seconds when the application requires millisecond precision.
- Using manual UTC offsets instead of the runtime's timezone-aware facilities.
When to use Unix timestamps
Excel is especially useful for auditing timestamp columns, spotting outliers, and validating a sample of imported API data before it reaches a database.
Related Timestamp Tools
Unix Timestamps in Excel: Epoch Seconds vs Excel Dates
Excel dates and Unix timestamps use different representations. Excel stores dates as serial day numbers, while a Unix timestamp stores elapsed time from the Unix Epoch. To convert between them, the key step is accounting for the number of days between Excel's date system and 1970-01-01, then converting days to seconds or milliseconds.
Useful formulas
For a UTC date/time stored in an Excel cell, a common seconds conversion is =(A1-DATE(1970,1,1))*86400. To turn Unix seconds in B1 back into an Excel date/time, use =DATE(1970,1,1)+B1/86400. The cell should then be formatted as a date/time. These formulas assume the cell value is interpreted in the intended timezone and that you want whole seconds rather than sub-second precision.
Example code
Quick reference
Practical checks
- Keep the source timezone clear before doing the formula conversion.
- Use 86400 seconds per day for whole-second conversions.
- Format output cells as dates after converting back from epoch seconds.
- Check whether the source contains milliseconds; if so, divide by 1000 before using the seconds formula.
Frequently Asked Questions: Practical Details
Why can Excel and Unix timestamps show different dates?
They use different storage conventions and may also have different timezone assumptions.
Can Excel handle Unix milliseconds?
Yes, but divide milliseconds by 1,000 before applying a seconds-based date formula, or incorporate the factor directly.
Does Excel's date system have historical quirks?
Yes. Legacy Excel compatibility includes a well-known 1900-date-system behavior, so historical dates should be tested rather than assumed.
Frequently Asked Questions
How does Excel represent the current Unix time?
Use the DATE facilities shown on this page and confirm the returned unit in the language documentation.
Which timestamp unit should I keep in an Excel column?
Keep the API contract visible in the sheet and convert the documented unit rather than relying on the number of digits in a cell.
What timezone should an Excel export use?
Use the spreadsheet timezone context consistently when exporting rows and compare the result against a known UTC value.