Ask Your Question

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

asked 2018-01-14 04:13:42 +0100

Dougm1966 gravatar image

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.

edit retag flag offensive close merge delete


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

Jim K gravatar imageJim K ( 2018-01-14 06:28:32 +0100 )edit

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.

Dougm1966 gravatar imageDougm1966 ( 2018-01-14 08:43:41 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-14 06:36:08 +0100

Jim K gravatar image

updated 2018-01-14 06:38:35 +0100

Try this formula.


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

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]

edit flag offensive delete link more


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'?

Dougm1966 gravatar imageDougm1966 ( 2018-01-14 08:46:46 +0100 )edit

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

librebel gravatar imagelibrebel ( 2018-01-14 18:33:20 +0100 )edit

Thank you...I get it now...although it's still returning an integer type number...

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

Dougm1966 gravatar imageDougm1966 ( 2018-01-15 07:51:02 +0100 )edit

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" )

librebel gravatar imagelibrebel ( 2018-01-15 13:54:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-14 04:13:42 +0100

Seen: 1,577 times

Last updated: Jan 14 '18