0

I downloaded the data from tradingview which was exported csv format but the time format is unreadable. please see below.

1502942400
1502949600
1502956800
1502964000
1502971200
1502978400
1502985600
1502992800
1503000000

How can I change it to yyyy / mm / dd hh : mm format by excel ?

Grateful if you advise. Thanks a lot.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
Ma Kurties
  • 1
  • 5

2 Answers2

1

Unix time stamps represent the number of seconds since 1 Jan 1970.

Excel stores date/time as days and fractions of a day since 1 Jan 1900.

There are 86400 seconds in a day (24*60*60)

So to convert:

=A1/86400+DATE(1970,1,1)

and format the result as you wish.

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

The numbers are in unix time format.

so just use this function on each number put this function in a module .

Public Function fromUnix(ts) As String
    fromUnix = Format(DateAdd("s", ts, "1/1/1970 00:00:00"), "yyyy / mm / dd mm:hh")
End Function

and use it like

=fromUnix ("1502949600") OR =fromUnix (Range("A1").value) ' or whatever cell.

credits >> the source for this function

xShen
  • 452
  • 4
  • 9