Re: [libreoffice-users] 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: users+unsubscribe@global.libreoffice.org
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