datetime - Converting unix time into date-time via excel

Question

Trying to convert 1504865618099.00 Unix time into a readable date time. I tried this:

=(UNIX + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

But it's not working.

Answer 1

• To convert the epoch(Unix-Time) to regular time like for the below timestamp

  Ex: 1517577336206

• First convert the value with the following function like below

  =LEFT(A1,10) & "." & RIGHT(A1,3)

• The output will be like below

  Ex: 1517577336.206

• Now Add the formula like below

  =(((B1/60)/60)/24)+DATE(1970,1,1)

• Now format the cell like below or required format(Custom format)

  m/d/yyyy h:mm:ss.000

Now example time comes like

2/2/2018 13:15:36.206

The three zeros are for milliseconds