Linux Epoch Time Converter in Excel: Formulas and Tips
To convert Linux epoch time in Excel, divide the timestamp by 86,400 and add the result to Excel’s starting date: use =A2/86400+DATE(1970,1,1) when cell A2 contains Unix seconds. Format the result as a date and time; for milliseconds, divide by 86,400,000 instead.
What Linux epoch time means
Linux and many other systems represent a timestamp as the number of seconds elapsed since 1970-01-01 00:00:00 UTC. This reference point is called the Unix epoch. The value usually counts whole seconds, but applications and data files may store milliseconds, microseconds, or nanoseconds instead.
The timestamp itself is a number, not a formatted date. For example, 0 represents the start of the epoch, while 1700000000 represents a later instant. Excel dates, by contrast, are stored as day counts with a fractional part for the time of day. The conversion divides seconds into days and offsets the result to the epoch date.
Convert epoch seconds in Excel
If the Linux timestamp is in cell A2, enter this formula in another cell:
=A2/86400+DATE(1970,1,1)
There are 86,400 seconds in a day: 24 hours × 60 minutes × 60 seconds. Dividing by that number converts the Unix value into an Excel day fraction; adding the date for January 1, 1970 places it on Excel’s calendar.
- Paste or type the epoch value into A2, keeping it numeric.
- Enter the formula in B2 and press Enter.
- Set B2’s number format to a date-and-time format. In many Excel versions, select the cell, open Format Cells, and choose a suitable date or custom format.
- For more rows, fill or copy the formula down.
With 1700000000 in A2, the formula gives 2023-11-14 22:13:20 when displayed in UTC. A format such as yyyy-mm-dd hh:mm:ss makes the components easy to read. The display format changes how Excel shows the value, not the underlying instant.
Convert milliseconds and other units
Check the timestamp’s unit before converting. A common mistake is to treat a millisecond value as seconds. If A2 contains milliseconds, use:
=A2/86400000+DATE(1970,1,1)
For example, 1700000000000 milliseconds represents the same instant as 1700000000 seconds. Since one day contains 86,400,000 milliseconds, dividing by that quantity produces the appropriate Excel day count.
For microseconds, divide by 86,400,000,000; for nanoseconds, divide by 86,400,000,000,000. Excel stores date-time values as floating-point numbers, so very fine precision may be rounded or lost. Standard display formats also omit fractions of a second unless you specify them, for example with a custom format that includes decimal places after seconds.
UTC, local time, and date formatting
Unix epoch timestamps identify an instant relative to UTC. Excel date-time values do not carry a time-zone label or automatically know that a number is UTC. The conversion formula therefore gives the UTC date and time as a plain Excel serial value; it does not automatically convert the result to your computer’s local time.
If you need a local display, you must know the applicable time-zone offset for that date. Adding a fixed number of hours can work for a specific known offset, but it may be wrong across daylight-saving changes or where local rules have changed. For reliable historical or future conversions across time zones, use a system or data tool that handles time-zone rules rather than assuming one constant offset.
Excel may display a serial number instead of a readable date if the result cell uses General or Number formatting. Apply a date-time format to show the calendar value. Conversely, do not interpret the formatted display as proof that the value is local time: formatting controls appearance, not time-zone conversion.
Common errors and practical fixes
- Wrong scale: A value with 13 digits is often milliseconds, while a 10-digit value is often seconds. Digit count is only a clue; confirm the source’s documentation or sample data.
- Text instead of a number: Imported timestamps can include spaces or be stored as text. If division returns an error, remove unwanted characters or convert the cell to a number before applying the formula.
- Unexpected calendar system: Excel workbooks can use different date systems. For ordinary modern Unix timestamps, the formula using
DATE(1970,1,1)avoids relying on a manually typed serial number and is generally the clearest approach. - Too much apparent precision: A displayed time may hide fractional seconds. Choose a suitable number format, and remember that Excel’s floating-point storage limits the precision available for very small fractions.
- Dates outside Excel’s range: Excel cannot display every possible timestamp as a date. Very old or far-future values may produce dates outside the supported range or otherwise unsuitable results.
Excel versus command-line conversion
Excel is useful when timestamps are already in a spreadsheet, when you need to convert many rows, or when you want to sort and filter the results alongside other data. A formula is transparent and can be checked against the original timestamp.
On Linux, command-line date utilities can convert timestamps without opening a spreadsheet, and scripts can automate repeated file processing. The exact command syntax can differ among systems and utilities, so consult the local manual or help output. Excel may be more convenient for inspection; a command-line workflow may be better for repeatable batch jobs. In either case, verify whether the input is seconds or another unit and whether the requested output should be UTC or local time.
Frequently Asked Questions
What is the Excel formula for a Linux timestamp?
For Unix seconds in A2, use =A2/86400+DATE(1970,1,1), then format the result as a date and time.
How do I convert a 13-digit Unix timestamp in Excel?
It is commonly a millisecond timestamp. If confirmed, use =A2/86400000+DATE(1970,1,1). Check the data source rather than relying only on the number of digits.
Does the Excel formula convert UTC to my local time?
No. It converts the epoch value to a UTC-based date and time, but Excel does not attach a time zone to the result. Apply a time-zone conversion separately if local time is required.
Why does Excel show a number instead of a date?
The result cell is probably formatted as General or Number. Change its number format to a date-time format, such as yyyy-mm-dd hh:mm:ss.
How can I convert an Excel date back to Unix seconds?
If the Excel date-time is in B2, use =(B2-DATE(1970,1,1))*86400. The result may include a fractional part if B2 contains fractions of a second; use rounding only if whole-second output is needed.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.