Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index

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?


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:
or in a shorter but less obvious form:
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).
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:
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.


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.