1

enter image description here

Referring to the above screenshot, i'm trying to crawl data from Singapore Stock Exchange, which the web content is loaded dynamically from an API call returning json, example here

I'm having some problem with the dates, which is given as a number by the json. For example, 1575491760000 is supposed to be 2019-12-04 20:36:00GMT.

After some trial and error, i've figured solution using R:

as.POSIXct(1575491760000/1000, origin="1970-01-01", tz = 'GMT')  
# not sure why need to divide the number by 1000 here but i guess this is the way to make it work

and the above code does return "2019-12-04 20:36:00 GMT" in R.

However, my question is there a solution to the above conversion in Excel? I've tried a few different ways but none of them can deal with such long data scenario (date + time format). Appreciated if anyone can provide a specific solution!

Jeremy
  • 379
  • 2
  • 11
  • Try janitor which had a clean ecel date function and the anytime package – MDEWITT Dec 10 '19 at 01:51
  • 1
    What you have is a timestamp and to convert that timestamp to a date in excel you can refer to it here https://stackoverflow.com/questions/16070868/converting-time-stamps-in-excel-to-dates. I think this maybe a duplicate question of that. – Kevin Ng Dec 10 '19 at 02:55

1 Answers1

8

Here's the Excel equivalent. 

=DATE(1970,1,1) + 1575491760000/(1000*60*60*24)
# 12/4/19 20:36:00  with cell formatting set to   m/d/yy h:mm:ss

Unix time increments one for every millisecond since 1/1/1970. Excel datetimes increment one for every day since 1/1/1900.

So to convert from UNIX time to excel, divide by the number of milliseconds in a day (1000*60*60*24) and add to the date 1/1/70 (25569 under the hood in Excel.)

Jon Spring
  • 55,165
  • 4
  • 35
  • 53
  • If they have a large number of timestamps, you would want to hard code the numbers and have a relative source for the timestamp. Like so: `=25569 + A1 / 86400000` – HackSlash Dec 14 '19 at 00:08
  • 1
    Thanks. I tried an example using all 1,048,462 rows available to me in Excel 2016. It took about 3 seconds with hard coded numbers and about 5 seconds without. I expected there to be more speedup, but that difference could help in some contexts. – Jon Spring Dec 14 '19 at 00:26