Convert epoch time to human readable in LibreOffice Calc

I have a epoch time in cell `H2` which has a value of `1517335200000`. I am trying to convert it to a human readable format which should return `30/01/2018 6:00:00 PM` in GMT.

I have tried to convert it with the formula `H2/86400+25569` which I got from the OpenOffice forum. The formula returns the value `17587319`. When I change the number format in LibreOffice Calc to `Date`, it returns the value of `06/09/-15484`. That's not the value I want. So, how can I get the value in dd/mm/yyyy hh:mm:ss format form?

If `H2` contains the number to transform (`1517335200000`).

1. Make `H3` contain the formula:

= H2/1000/(60*60*24) + 25569

Which will return the number 43130.75.

2. Change format of cell `H3` to date. Either:

• Press Shift - Ctrl - 3
• Select Format --> Number Format --> Date
• Select Format --> Cells (a window opens) --> Numbers - Date - Format
3. Change format of the `H3` cell to the required date format:

• Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
4. Expand width of cell if not wide enough to show the desired format (hint: three # appear).

Why:

Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:

1. Get the correct number (last formula):

``````H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400      + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400      +   25569
``````

But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:

``````H3 = H2/1000/86400+25569  =  43130.75
``````
2. Change the format of `H3` to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:

``````01/30/2018 18:00:00
``````

in H3.

• I believe the the 3 zeros at the end are microsecond. – Fazle Rabbi Feb 2 '18 at 6:46
• @FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor `86400*1000` and the rest works the same. – Isaac Feb 2 '18 at 6:53
• any way to add the value of `12/30/1899` without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far `=(H2/(86400*1000))+25569` – Fazle Rabbi Feb 2 '18 at 7:01
• Format H3 as a date and time, that's the minimum needed. @FazleRabbi – Isaac Feb 2 '18 at 7:02
• Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc. – David Tonhofer Feb 26 at 10:49