2

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.

crichavin
  • 4,672
  • 10
  • 50
  • 95
  • That's a timestamp in ms since epoch: `new Date(1388624400000)`. Do you need to do this in Excel? – tadman Apr 09 '14 at 19:54
  • This earlier article may help: http://stackoverflow.com/questions/5806110/convert-json-date-to-mm-dd-yyyy-format – Almetraet Apr 09 '14 at 20:01

2 Answers2

2

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)

Community
  • 1
  • 1
crichavin
  • 4,672
  • 10
  • 50
  • 95
  • It dnt wrks for me . "/Date(1427260962827)/" this is my string it showing that Date is not defined. – A R S Mar 25 '15 at 12:10
  • `=((MID(A1, 1, 10))/86400)+DATE(1970,1,1)` if you have just the js date – jcollum May 26 '16 at 22:20
  • edited the mid to take from place 8, added what the result should look like, and added the hex2dec without which the division by 86400 fails. – pashute Nov 21 '16 at 19:55
  • @pashute The way I originally had it works. It returns 1/2/2014. Which is what I get if I put ` new Date(1388624400000)` into the console in Google Chrome browser. With your edits, it is returning an incorrect date of 1/12/9663. – crichavin Nov 22 '16 at 00:42
  • @ChadRichardson But of course. The question was with '/Date(123456...' and NOT for 'new Date(123456...'. You need the number portion after the opening parenthesis. ' You can use: =MID(A1, Find("(", A1) + 1, 10) And for the full formula: = (Hex2Dec( MID(A1, Find("(",A1) + 1, 10) ) / 86400) + DATE(1970, 1,1) See here: http://www.techrepublic.com/article/using-excels-find-and-mid-to-extract-a-substring-when-you-dont-know-the-start-point/ – pashute Nov 30 '16 at 18:10
  • and use this to check yourself: http://www.freeformatter.com/epoch-timestamp-to-date-converter.html – pashute Nov 30 '16 at 22:43
0

I doubt if you're still looking for an answer 7 years later, but I found this helpful: https://www.mrexcel.com/board/threads/converting-json-date-time-format.933941/

It's the answer that jcollum gives above: Extract the value 1388624400000 from the cell Divide it by 86400000 Add 25569* And you get "1/2/2014"

*25569 is the DATE value of 1/1/1970 in Excel

Colm Flynn
  • 1
  • 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. – Eric Jul 13 '21 at 22:56