Problem converting unix time from exported CVS file

Mykola · January 16, 2017, 12:39am

I’m trying to convert Unix time exported from the graph. Found existing numbers contain extra three zeros at the end.

Eugene · January 16, 2017, 2:24am

Those are milliseconds from 1970

Mykola · January 16, 2017, 2:31am

How I still can convert to human time in excel ?.

Jamin · January 16, 2017, 2:35am

Please use google next time…

Costas · January 16, 2017, 2:40am

also a search of this site with “Excel Date” brings up Data format in History Graph

Mykola · January 16, 2017, 3:05am

Please do it on your own and get a result before refer to link which I read twice before asked this question. I tried all methods and wasn’t able to convert a unix timestamp to date.
Even on mac it’s impossible:

And I’m still insisting on unnecessary have last three zeros at the and of timestamp, other way somebody can explain wat I’m doing wrong to convert Unix time.

Jamin · January 16, 2017, 3:33am

I don’t have this probem. My CSV doesnt have the extra 0’s hense the tutorial works fine.

Costas · January 16, 2017, 3:57am

milliseconds in a second = 1000
as pointed out by @eugene you are seeing “millisecond time” not “second time” so simply divide what you have by 1000 in the Excel cell. Then look at the rest of the formulae below for date and time.

Date hour mins secs
1484525700000 1484525700 16/01/2017 900 0 15 0
=B3/1000 =(C3/86400)+25569 =MOD(C3+(25569*86400),86400) =INT(E3/3600) =INT(E3/60) =MOD(E3,60)
mod stuff
this column is formatted as date