4

My client receives a spreadsheet with a number of columns, one being a "date". Only the date turns out to be formatted as Date(1292291582263-0700) (a JSON date it seems).

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

Does anyone know how to parse and convert this JSON date format into a MM/DD/YYYY format? I have read lots of solutions on SO that are in Javascript, C#, or ASP.NET, etc but all I have to work with is Excel 2010 and VBA code for this project. Is there way to arrive at a readable format as I need?

Improve this question
2
  • 1
    There is no date type in JSON. See the sidebar on the right: json.org Commented Apr 27, 2011 at 14:56
  • @Matt: OK, but isnt there some way to convert this string into a readable date format (without the use of Javascript, etc?) Just a hand written function or something? Commented Apr 27, 2011 at 15:02

2 Answers 2

Reset to default
6

Millisecond Epoch time with a +/- 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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

This function take care of date before 1971-01-01 and some problem with overflow.

Public Function Convert_Microsoft_Json_Date_To_Date(strMicrosoftDate As String) As Date 'Convert_Microsoft_Json_Date_To_Date("/Date(-2208970800000-0530)/") => 1900-01-01 'Convert_Microsoft_Json_Date_To_Date("/Date(2208970800000-0530)/") => 2039-12-31 14:00:00 Dim strProcedureName As String: strProcedureName = "Convert_Microsoft_Json_Date_To_Date" Dim lngDateNumber As Long Dim strOffsetSign As String Dim strOffsetHours As String Dim strOffsetMinutes As String Dim dteDateNoOffset As Date Dim dteRealDate As Date Dim curSecondToAdd As Currency '+ or - Dim curSecondLeft As Currency Dim curSecondMax As Currency Dim IsOffsetExist As Boolean On Error GoTo err_ strMicrosoftDate = Replace(strMicrosoftDate, "/", "") strMicrosoftDate = Replace(strMicrosoftDate, "(", "") strMicrosoftDate = Replace(strMicrosoftDate, ")", "") strMicrosoftDate = Replace(strMicrosoftDate, "Date", "") strOffsetSign = Left(Right(strMicrosoftDate, 5), 1) strOffsetHours = Left(Right(strMicrosoftDate, 4), 2) strOffsetMinutes = Right(strMicrosoftDate, 2) IsOffsetExist = strOffsetSign = "+" Or strOffsetSign = "-" If IsOffsetExist Then 'Remove the offset part if exist strMicrosoftDate = Left(strMicrosoftDate, Len(strMicrosoftDate) - 5) End If curSecondMax = 1000000000# 'if curSecondToAdd is to high we get overflow, and I do it in 2 step below to get my date curSecondToAdd = CCur(strMicrosoftDate) / 1000 'Convert miliseconds to seconds If Abs(curSecondToAdd) > curSecondMax Then If curSecondToAdd >= 0 Then dteDateNoOffset = DateAdd("s", curSecondToAdd - curSecondMax, DateSerial(1970, 1, 1)) dteDateNoOffset = DateAdd("s", curSecondMax, dteDateNoOffset) Else dteDateNoOffset = DateAdd("s", curSecondToAdd + curSecondMax, DateSerial(1970, 1, 1)) dteDateNoOffset = DateAdd("s", -curSecondMax, dteDateNoOffset) End If Else dteDateNoOffset = DateAdd("s", curSecondToAdd, DateSerial(1970, 1, 1)) End If 'Debug.Print "Date no offset: " & dteDateNoOffset If IsOffsetExist Then dteRealDate = DateAdd("h", CInt(strOffsetSign & strOffsetHours), dteDateNoOffset) dteRealDate = DateAdd("n", CInt(strOffsetSign & strOffsetMinutes), dteRealDate) Else dteRealDate = dteDateNoOffset End If 'Debug.Print "Date real: " & dteRealDate Convert_Microsoft_Json_Date_To_Date = dteRealDate err_exit: Exit Function err_: Select Case Err.Number Case Else MsgBox Err.Description & " | " & Err.Number & vbCrLf & "Procedure: " & strProcedureName & IIf(Erl <> 0, vbCrLf & "Ligne: " & Erl, ""), vbCritical Resume err_exit Resume End Select End Function
Improve this answer

1 Comment

Please put the explanation in your answer instead of as a comment.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.