android calendar dates

users
#1

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

#2

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.