Convert epoch time to human readable in LibreOffice Calc

Question

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?

There are [many definitions of Epoch](https://en.wikipedia.org/wiki/Epoch_(computing)#Notable_epoch_dates_in_computing), additionally, this is not in seconds, but in milliseconds. — sebix, Aug 07 '20 at 19:06

score 53 · Accepted Answer · 2019-12-30T20:30:55.117

If H2 contains the number to transform (1517335200000).

Make H3 contain the formula:

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

Which will return the number 43130.75.
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
Change format of the H3 cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
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:

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 given in milliseconds. So, divide by 1000. With that change, the formula gives:
```
H3 = H2/1000/86400+25569  =  43130.75
```
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.

Of course, since Unix epoch time is always based on UTC (+0 meridian), the result above needs to be shifted as many hours as the local Time zone is distant from UTC. So, to get the local time, if the Time zone is Pacific standard time GMT-8, we need to add (-8) hours. The formula for H3 with the local time zone (-8) in H4 would be:

H3 = H2/1000/86400 + 25569 + H4/24 = 43130.416666

And presented as:

01/30/2018 10:00:00

if the format of H3 is set to such time format.

score 1 · Answer 2 · edited Feb 07 '23 at 11:03

Just in case somebody comes across this in future. Nothing wrong with the above I just didn't understand it well so I'm saying it a different way in hopes it helps others.

Tl;dr.

The formula is

=UnixTime/86400 + 25569
=H2/86400 + 25569

Dates seem to be represented as floating point days since libreoffices epoch of DATE(1899,12,30) not as seconds.

To convert unix epoch of integer seconds since 1970 to floating point days divide by the seconds in a day or 86400.

So to do the conversion.

libreoffice datetime = (UnixEpoch / (Seconds in a day 86400)) + the number of days (not seconds) between the epochs.

worked example showing the values you get along the way 1675781528 unix epoch (2023-02-07T14:52:08)

1675781528 / 86400 = 19395.619537037 days since 1970

(DATE(1970,1,1) - DATE(1899,12,30)) = 25569 days between 1890 and 1970

25569 + 19395.619537037 = 44964.62 days since 1899

Convert epoch time to human readable in LibreOffice Calc

2 Answers2

Why: