Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

datetime - Converting unix time into date-time via excel

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
  • 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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...