Convert JSON date to MM / DD / YYYY?

Question

My client receives a table with the number of columns, one of which is a "date". Only the date is formatted as Date(1292291582263-0700) (it looks like a JSON date).

I need to convert and work with this JSON date in MM / DD / YYYY format, elsewhere in this spreadsheet (VBA) code.

Does anyone know how to parse and convert this JSON date format to MM / DD / YYYY format? I read a lot of SO solutions that are in Javascript, C # or ASP.NET, etc., But the ones I have to work with are the Excel 2010 and VBA code for this project . Is there a way to get a readable format as needed?

Answer 1

Time in millisecond Time with +/- offset?

 Const test = "1292291582263-0700" Dim dt As String: dt = Left$(test, 13) Dim off As String: off = Mid$(test, 14) Dim d As Date: d = DateAdd("s", CCur(dt) / 1000, "01/01/1970") Debug.Print d <<< 14/12/2010 01:53:02 d = DateAdd("h", Left$(off, 3), d) d = DateAdd("n", Right$(off, 2), d) Debug.Print d <<< 13/12/2010 18:53:02