2

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?

3

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

H3 = H2/(60*60*24)+Diference from 1/1/1970 and 12/30/1899 in days
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. With that change, the formula gives:

H3 = 1517335200/86400+25569  =  43130.75

days ahead of 30/12/1899. Make H4=12/30/1899 and then H5 is

H5 = H3 + H4 

Change the format to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:

H5 = 01/30/2018 18:00:00
  • 1
    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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.