search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.76k stars 568 forks source link

Json Date Format To "yyyy-mm-dd" #197

Open hssndrms opened 3 years ago

hssndrms commented 3 years ago

hi, this project saves lives. Thank you for your work. I have a question. The API I use returns data as "/Date (-2208999600000)/" as the date format. how do i convert this to UTC date format.

json = {"AccountClosingDate": "/Date (-2208999600000)/"}

RayCulp commented 3 years ago

I think what you're looking at there is the time since January 1, 1970, in milliseconds.

If I put the value -2208999600000 in cell A1 in an Excel sheet and then this formula next to it =(A1/1000/60/60/24)+DATE(1970;1;1) in cell B1 and format it as Custom like this TT/MM/JJJJ h:mm:ss,000, the result is 01/01/1900 21:00:00,000 .

Please note, I'm on a computer that uses German number formatting, so a comma is a semicolon and vice versa and TT/MM/JJJJ would be DD/MM/YYYY on an English system.