XML Date/Time format into Excel not converting properly

%3CLINGO-SUB%20id%3D%22lingo-sub-1424642%22%20slang%3D%22en-US%22%3EXML%20Date%2FTime%20format%20into%20Excel%20not%20converting%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424642%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%26nbsp%3B%20I%20installed%20a%20XML%20file%20into%20Excel%20so%20I%20could%20read%20it%20easier%2C%20I'm%20a%20novice.%26nbsp%3B%20However%2C%20when%20I%20did%20the%20date%2Ftime%20field%20did%20not%20carry%20over%20to%20a%20readable%20format.%26nbsp%3B%20Please%20see%20screen%20shot.%26nbsp%3B%20Would%20anyone%20know%20how%20to%20convert%20this%3F%26nbsp%3B%20I%20have%20tried%20a%20few%20schemas%20like%26nbsp%3B%3CSPAN%3Eyyyy-mm-ddThh%3Amm%3Ass%2B%2F-hh%3Amm%2C%20but%20nothing%20is%20working.%26nbsp%3B%20Thank%20you%20ahead%20of%20time.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20prob.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195165iE51EC9DCC9BF1647%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excel%20prob.jpg%22%20alt%3D%22excel%20prob.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1424642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424871%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20Date%2FTime%20format%20into%20Excel%20not%20converting%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682633%22%20target%3D%22_blank%22%3E%40wendi78%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20you%20have%2013-digits%20UNIX%20timestamps%2C%20i.e.%20with%20milliseconds.%20You%20may%20convert%20it%20on%20Windows%20datetime%20in%20Excel%20as%20below%2C%20using%20that%20formula%20and%20applying%20custom%20format%20to%20the%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195184iD9D3DE7B784F5753%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(((A1%2F1000%2F60)%2F60)%2F24)%2BDATE(1970%2C1%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425088%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20Date%2FTime%20format%20into%20Excel%20not%20converting%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425088%22%20slang%3D%22en-US%22%3EThis%20worked%20perfectly!%20Thank%20you%20so%20much%20for%20your%20help%20as%20I%20greatly%20appreciate%20it!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425151%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20Date%2FTime%20format%20into%20Excel%20not%20converting%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682633%22%20target%3D%22_blank%22%3E%40wendi78%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello.  I installed a XML file into Excel so I could read it easier, I'm a novice.  However, when I did the date/time field did not carry over to a readable format.  Please see screen shot.  Would anyone know how to convert this?  I have tried a few schemas like yyyy-mm-ddThh:mm:ss+/-hh:mm, but nothing is working.  Thank you ahead of time.

 

excel prob.jpg

3 Replies

@wendi78 

It looks like you have 13-digits UNIX timestamps, i.e. with milliseconds. You may convert it on Windows datetime in Excel as below, using that formula and applying custom format to the result

image.png

 

=(((A1/1000/60)/60)/24)+DATE(1970,1,1)

 

Highlighted
This worked perfectly! Thank you so much for your help as I greatly appreciate it!
Highlighted

@wendi78 , you are welcome, glad to help