How to Convert Unix Milliseconds to Excel Dates in WPS Spreadsheets
Question details
User needs to convert Unix epoch timestamps expressed in milliseconds since January 1, 1970 into readable date or date-time values in WPS Spreadsheets after importing a CSV file.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Dates downloaded in a CSV file appear as large scientific-notation numbers, such as 1.65777E+12, because the source system exported Unix timestamps in milliseconds.
- Observed behavior
- Timestamp values display as large scientific-notation numbers instead of real dates.
Confirm that the timestamp column is imported as numbers rather than text, and identify the first data cell, for example A2, so the conversion formula can be filled down the whole range.
Convert Unix milliseconds with the DATE formula and date formatting
Use this solution when CSV timestamp values are numeric Unix milliseconds and you need a standard date or date-time column in WPS Spreadsheets.
Unix timestamps count time since January 1, 1970. When the value is stored in milliseconds, divide it by 86,400,000, the number of milliseconds in one day, and add that result to the WPS Spreadsheets serial date for January 1, 1970.
Click the empty cell where the converted date should appear, for example C2, next to the first timestamp in A2.
Type =DATE(1970,1,1)+A2/86400000 and press Enter. Adjust A2 to the actual first timestamp cell.
With the result cell selected, press Ctrl+1 to open Format Cells, choose Date or Custom, and pick a format such as yyyy-mm-dd hh:mm:ss.
Drag the fill handle from the result cell downward to apply the formula to all remaining timestamp rows.
Convert Unix millisecond timestamps to dates directly in WPS Spreadsheets
WPS Spreadsheets can complete this conversion with a simple formula and standard date formatting, so you can clean CSV exports and analyze timestamps without leaving the spreadsheet.
- 1. Open the CSV file in WPS Spreadsheets: Launch WPS Office, click Open, and select the CSV file containing the Unix millisecond timestamps.
- 2. Insert a helper column for converted dates: Click the column letter next to your timestamp column, right-click, and choose Insert to create a new column.
- 3. Enter the DATE formula: In the first cell of the helper column, type =DATE(1970,1,1)+A2/86400000 and press Enter.
- 4. Apply date formatting and fill down: Press Ctrl+1, set the desired Date or Custom format, then drag the fill handle down through the dataset.
Frequently Asked Questions
Why does my CSV date show as 1.65777E+12 in WPS Spreadsheets?
The value is a Unix timestamp in milliseconds since January 1, 1970. Spreadsheets display large numbers in scientific notation until you convert and format them as dates.
Can this formula handle Unix timestamps in seconds instead of milliseconds?
Yes. For Unix seconds, use =DATE(1970,1,1)+A2/86400 instead of dividing by 86,400,000.
How do I show both date and time after converting?
Select the formula cells, press Ctrl+1, choose Custom, and enter a format such as yyyy-mm-dd hh:mm:ss.
Will the conversion update if I change the original timestamp?
Yes. The formula references the timestamp cell, so whenever the original Unix millisecond value changes, the converted date updates automatically.
Does this formula work in Microsoft Excel too?
Yes. The DATE formula and the 86,400,000 divisor work the same way in WPS Spreadsheets and Microsoft Excel because both store dates as serial numbers.
