datedate-formattinglibreofficecalc

How to convert formatted date to unix epoch in Libreoffice calc


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.


Solution

  • 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!