0

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>
</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?

bpeikes
  • 3,495
  • 9
  • 42
  • 80

1 Answers1

0

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!

bouvierr
  • 3,563
  • 3
  • 27
  • 32