Convert JSON Date /Date(1388624400000)/ to Date in Excel

Question

I have a web extract of data in Excel that has a date column with /Date(1388624400000)/. I need to convert it to date in Excel.

Solution

Was able to deduce it from here.

1. Assume the string /Date(1388624400000)/ is in cell A1

2. Extract the number portion and Trim the 13 digit number to the first 10,i.e. =MID(A1,8,10); You should get: 1388624400. store this number in B1

3. Then in C1, turn the hex number into a decimal one with =hex2dec(B1).

4. Then use the formula =(C1/86400)+DATE(1970,1,1)

   And for short, assuming the date is in A1:

   =(Hex2Dec( MID(A1, 8, 10) ) / 86400) + DATE(1970, 1,1)

Comments

[Eric]

It's still helpful to post answers even if the original poster isn't looking for the answer any more because anyone else who comes across this might need that answer. It would be helpful if you included a workable code snippet in your answer, and be careful depending on external links, they die all the time so try to make sure your answer can stand on its own.