How to convert formatted date to unix epoch in Libreoffice calc

Question

I have column with cell format date or time (DD.MM.YYYY HH:MM:SS) and values like 03.12.2013 14:01:49 04.12.2013 10:19:27 04.12.2013 12:44:56 04.12.2013 14:20:12 04.12.2013 18:30:21 I need those values converted to unix epoch (seconds since 1970). Somehow it feels like the values are not recognized as dates, but rather as strings. I tried different formats, had little luck with dates without time.

Answer 1

Operations performed on date data should be automatic provided that the cells are formatted as as a user defined DD.MM.YYYY HH:MM:SS in the 'Format' > 'Cells' > 'Numbers' tab.

If you're using the standard settings, LibreOffice Calc uses 12/30/1899 as it's default date. So the first step is getting the number of days between 12/30/1899 and 1/1/1970:

=(DATE(1970,1,1) - DATE(1899,12,30)) = 25569

Number of seconds in a day:

=(60 * 60 * 24) = 86400

If, for example, in cell A2 you have the date 03.12.2013 14:01:49. I subtract the difference between Calc's default date and the Unix Epoch we just calculated, and multiply it by the number of seconds in a day:

=(A2 - 25569) * 86400

The result is a value of 1363096909 which is the Epoch time in seconds. If you need it in milliseconds, multiply the equation by 1000.

If it's something you use a lot, you can create a custom function that does this. Go to Tools > Macros > Edit Macros, and type out the following into whichever module comes up:

REM  *****  BASIC  *****

Function EPOCH(date_cell)
EPOCH = (date_cell - 25569)*86400
End Function

Close the macro IDE, and now you can use your EPOCH() like any other function!