How do I convert a column of text to "Date AND Time". My number represents both.

English
#1

I’m trying to convert an xml file of sms’s. The date string is not only the date but the time of day.

I can see how to convert one or the other but I haven’t found a way to convert the whole string…example…

This number: 1448525537435
Produces this: 19/12/-3741

This number: 1448522062966
Produces this: 19/12/-3741

and this number: 1448522039343
also produces: 19/12/-3741

etc etc etc.

#2

What is the desired result? That is, what dates are they really?

#3

Thanks, Jim… It’s an xml file of text messages from an Android phone. The date is this long number and I’ve tried using the ‘text to columns’ and then clicked the selection and changed it to ‘column type’ to the different date formats and I get the results you see above.

#4

Thanks, Jim… It’s an xml file of text messages from an Android phone. The date is this long number and I’ve tried using the ‘text to columns’ and then clicked the selection and changed it to ‘column type’ to the different date formats and I get the results you see above.

#5

Try this formula.

=1448525537435/1000/86400+25569

The result when formatted as a date/time is 2015-11-26 08:12:17.

Explanation: Divide by 1000 to convert milliseconds to seconds. Then from Convert UNIX timestamp (View topic) • Apache OpenOffice Community Forum

Unix timestamp is seconds since 1st January 1970.
So: divide by number of seconds per day (86400), gives you the number of days since then, add 25569 (which is 1.1.1970 0:00h) in OOo Calc [Tools>Options>LibreOffice Calc>Calculate>start date as 30.12.1899]

#6

Are you suggesting I add the = at the beginning and /1000/86400+25569 at the end of each entry then use the ‘text to column’?

#7

i think not ; just try the instructions from “ambparker” from the link posted in the answer above by @jimk

#8

Thank you…I get it now…although it’s still returning an integer type number…

For example
1448525537435 = 12/12/-17665 20:50:35

#9

The input number must also be divided by 1000, as explained in the original answer.

Then i get 1448525537435/1000/86400+25569 —> donderdag 26 november 2015 08:12

( in my NL locale with date format = “NNNND MMMM JJJJ UU:MM” )

#10

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.