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.