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.

Rooker

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!

Encyclopedic

How does this account for the epoch being in the UTC timezone? – Aerospace

It does not account for UTC, as the question did not ask for it to be in UTC. To do so, you will either have to account for that by adding or subtracting hours in the calculation, or setting your default time in Calc to UTC. – Encyclopedic

Yeah, I know the question didn't ask for it specifically, but standard epoch timestamps are always in UTC. Thanks for the extra info. – Aerospace

@Walf, yes everything except for the user data (unless they're UTC) is in UTC. If you'd like to edit the function to take a second arg like time_zone feel free, otherwise I will update when I get a chance. It could be off by quite the further you are from UTC and using local time. I took using UTC by default for granted. – Encyclopedic

Answer 2

This formula worked for me, where the others above did not:

= DATE( 1970; 1; 1 ) + ( A1 / 86400 )

Snippy