To convert a millisecond timestamp to a DateTime in LibreOffice Calc, you must divide the timestamp by 86,400,000 (the number of milliseconds in a day) and add the Unix epoch start date. This calculation transforms the raw numeric value into a serial date number that LibreOffice can interpret as a calendar date and time.
Understanding the Millisecond Timestamp
A millisecond timestamp represents the total number of milliseconds elapsed since January 1, 1970, at 00:00:00 UTC, known as the Unix Epoch. In LibreOffice Calc, dates are stored as serial numbers where the integer part represents the number of days since December 30, 1899, and the fractional part represents the time of day. Because these two systems use different base dates and units, a direct conversion is not possible without a mathematical adjustment. You need to bridge the gap between the Unix Epoch and LibreOffice's internal date system while converting the unit from milliseconds to days.
The Conversion Formula
The core of the conversion lies in dividing the timestamp by the number of milliseconds in a single day. There are 1,000 milliseconds in a second, 60 seconds in a minute, 60 minutes in an hour, and 24 hours in a day. Multiplying these values gives us 86,400,000 milliseconds per day. By dividing your timestamp cell by this constant, you obtain the number of days since the Unix Epoch. However, because LibreOffice uses December 30, 1899, as its zero date rather than January 1, 1970, you must also add the number of days between these two epochs. The difference is approximately 25,569 days. Therefore, the complete formula structure involves dividing the source value by 86,400,000 and adding 25,569.
Step-by-Step Implementation in Calc
Follow these steps to apply the conversion in your spreadsheet. Ensure that your timestamp data is located in column A, starting at row 2, with headers in row 1.
- Select the Target Cell: Click on cell B2, which will hold the converted DateTime value for the first entry.
- Enter the Formula: Type the following formula exactly as shown, replacing
A2 with the actual reference to your timestamp cell: =A2/86400000+25569. This formula performs the division and adds the epoch offset simultaneously.
- Apply Date Formatting: Right-click the cell B2 and select Format Cells. Navigate to the Numbers tab and choose Date or Time depending on whether you want to see both or just one component. If you need both, select a custom format like
YYYY-MM-DD HH:MM:SS.
- Fill Down: Copy the cell B2 and paste it down the column to apply the conversion to all other timestamps. Alternatively, double-click the fill handle (the small square at the bottom right of the selection) to auto-fill to the last row of adjacent data.
If you encounter issues with the output displaying as a series of hashes (
#####), it usually means the column width is too narrow to display the formatted date. Simply widen column B to resolve this visual issue. For more complex table references, consider reviewing how to transform formula field references properly, as seen in our guide on
Transforming Formula Field Reference to Table Reference in LibreOffice Calc.
Handling Time Zones
Unix timestamps are inherently based on Coordinated Universal Time (UTC). If your data originates from a specific time zone, you may need to adjust the result to reflect local time. LibreOffice does not have a built-in function to automatically detect or convert time zones within standard formulas without using scripts or external libraries. To manually adjust for a time zone offset, you can add or subtract hours from the result. Since one day equals 24 hours, one hour equals 1/24th of a day. To add 5 hours for a UTC+5 time zone, modify the formula to:
=A2/86400000+25569+5/24. Subtract the fraction if you are moving to a negative offset. Be cautious when dealing with Daylight Saving Time transitions, as manual adjustments do not account for these shifts automatically.
Importing CSV Data Correctly
Often, the difficulty arises not from the formula but from how the data is imported. When importing CSV files containing timestamps, LibreOffice might treat them as text or generic numbers. If your cells show the timestamp as text, the formula will return an error. You must ensure the column type is recognized as a number during import. Use the Text Import dialog to explicitly set the column type to "Standard" or "Date" if applicable, though "Standard" is safest for raw timestamps. For detailed instructions on managing column types during import, refer to our article on
Changing CSV Column Types. This prevents parsing errors before you even attempt to apply the conversion formula.
Common Errors and Troubleshooting
If the formula returns a #DIV/0! error, check for empty cells in the source column. The divisor cannot be zero, so empty cells cause division failures. Wrap your formula in an IF statement to handle blanks gracefully:
=IF(A2="","",A2/86400000+25569). Another common issue is the #NAME? error, which indicates a typo in the formula or language settings. Ensure that your formula syntax matches your LibreOffice language configuration. In some European versions, commas might be replaced by semicolons as argument separators, although this specific formula has no arguments requiring separators. Always verify that the cell containing the timestamp is indeed numeric. If it contains hidden characters or spaces, use the TRIM function to clean the data first.
Frequently Asked Questions
Why does my converted date show as a random year?
This happens if you forget to add the epoch offset of 25,569 days. Without this addition, LibreOffice calculates the date relative to December 30, 1899, resulting in a date far in the past or future depending on the magnitude of the timestamp. Always include the +25569 component to align with the correct calendar era.
Can I convert seconds instead of milliseconds?
Yes, simply change the divisor. For seconds, divide by 86,400 (seconds in a day) instead of 86,400,000. The rest of the formula remains identical:
=A2/86400+25569. Ensure your input data is actually in seconds, as mixing units will produce incorrect results.
How do I remove the time portion and keep only the date?
You can use the INT function to truncate the decimal part of the serial number, which represents the time. Apply the formula
=INT(A2/86400000+25569) and format the cell as a short date. This effectively strips the hours, minutes, and seconds, leaving only the calendar date.
Does this method work for negative timestamps?
Negative timestamps represent dates before January 1, 1970. The formula works correctly for these values as well, producing dates prior to 1970. However, be aware that very early dates might exceed the limits of some legacy date systems or display incorrectly in older software versions.
What if my timestamp includes microseconds?
If your timestamp has more precision than milliseconds, such as nanoseconds or microseconds, you must adjust the divisor accordingly. For microseconds, divide by 86,400,000,000. For nanoseconds, divide by 86,400,000,000,000. The epoch offset remains the same regardless of the sub-second precision.