XML Date/Time format into Excel not converting properly

Copper 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)

 

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

@wendi78 , you are welcome, glad to help