Menu
I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.
Thank you in advance :)
P.S. I don't know any programming languages
806
You can simply use the fromtimestamp function from the DateTime module to get a date from a UNIX timestamp. This function takes the timestamp as input and returns the corresponding DateTime object to timestamp.
In an Excel sheet, select the cells you want to format. Press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Custom from the Category list and type the date format you want in the Type box. Click OK to save the changes.
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/ Tested in libreoffice
96
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
31
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
