android calendar dates

classic Classic list List threaded Threaded
2 messages Options
bjlockie bjlockie
Reply | Threaded
Open this post in threaded view
|

android calendar dates

I backed up the calendar on my android phone.
It is CSV and I want to import it into Calc.
It has these numbers which I think are the dates.
Does anyone know how to convert then to Calc dates?

1459397923718
1427831701482
1459397923718
1491348608631
1526254831608


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: android calendar dates

James wrote:

> I backed up the calendar on my android phone.
> It is CSV and I want to import it into Calc.
> It has these numbers which I think are the dates.
> Does anyone know how to convert then to Calc dates?
>
> 1459397923718
> 1427831701482
> 1459397923718
> 1491348608631
> 1526254831608

Not knowing which dates those numbers are supposed to represent, it's
difficult to be sure, but looks like they might be milliseconds since
the Unix epoch (midnight on 1st January 1970). Calc represents dates as
a number of days since midnight on 30th December 1899 (by default; there
are a few options to choose from for compatibility with other applications).

To convert within Calc:
   =(A1/1000/60/60/24)+DATE(1970,1,1)-DATE(1899,12,30)
or in a shorter but less obvious form:
   =A1/86400000+25569
Where the number from Android is in A1 in either case.

This converts a number in milliseconds to a number of days, then
adds/subtracts appropriate values to adjust for the different epoch. The
cell containing the formula can then be formatted in the desired
date/time format and used for calculations.

The above list then becomes (output formatted to show date and time):
   1459397923718 | 2016-03-31 04:18:44
   1427831701482 | 2015-03-31 19:55:01
   1459397923718 | 2016-03-31 04:18:44
   1491348608631 | 2017-04-04 23:30:09
   1526254831608 | 2018-05-13 23:40:32

The times seem a bit strange, but it could be that they're only intended
to be shown as dates and the time is fairly arbitrary (possibly the time
when the entry was created, with the date changed as necessary) - I'm
just guessing though.


Since these values contain a non-zero time, you may need to make the
time part zero if doing calculations in days (e.g. days between two events).
   =FLOOR(B1)
With the value calculated as above in B1 should do that, since the value
is in days, with the fractional part indicating the time. If you don't
need the time part for any purpose, you could just wrap the whole
expression in FLOOR() when converting:
   =FLOOR((A1/1000/60/60/24)+DATE(1970,1,1)-DATE(1899,12,30))
Resulting in:
   1459397923718 | 2016-03-31 00:00:00
   1427831701482 | 2015-03-31 00:00:00
   1459397923718 | 2016-03-31 00:00:00
   1491348608631 | 2017-04-04 00:00:00
   1526254831608 | 2018-05-13 00:00:00

Note that just changing the formatting to only display the date does NOT
affect the value used for calculations; you'd need to adjust the value
used in the calculations.

--
Mark.


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy