Excel 2007 will not reformat an xml web query which has a datetime field

I have a web service which returns XML which looks like:

<ArrayOfPosition>
<Position>
<Amount>9</Amount>
<Symbol>Bob</Symbol>
<UpdateTime>2011-04-21T11:34:34.234234234-04:00</UpdateTime>
<UpdateTimeStr>2011-04-21 11:22:33</UpdateTimeStr>
</Position>
<Position>
<Amount>10</Amount>
<Symbol>Fred</Symbol>
<UpdateTime>2011-04-21T11:34:34.234234234-04:00</UpdateTime>
<UpdateTimeStr>2011-04-21 11:22:44</UpdateTimeStr>
&l开发者_高级运维t;/Position>
</ArrayOfPosition>

It comes into Excel without problems but no matter what I do I can't change the formatting on the UpdateTime or UpdateTimeStr columns in the spreadsheet. Excel seems to refuse to recognize this data as a datetime field.

I had a feeling that it would not be able to figure out that the field was a date and time, so I added a field called UpdateTimeStr which has the time formatted in a way I expected Excel to understand, but alas, no go. Any ideas?

One solution is to use a formula in Excel to reuse the textual data you import from XML. For example, you can write a formula like: "=D2+0" (D2 would be the cell where the UpdateTimeStr is stored) to get the numerical representation of the UpdateTimeStr value (ex: 40654.47399). You can then format this numerical value as a Date any way you like.

You can do this little trick in a separate column to calculate all numerical representations for the entire UpdateTimeStr column. Then you format this new column (with numbers) and paste it over the original UpdateTimeStr column.

I hope this helps you!