Chad Richardson Chad Richardson - 1 year ago 107
Javascript Question

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

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.

Answer Source

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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download