Excel XML, how can I get Excel to display my DateTime field in yyyy-MM-dd format?

Question

I generate an XML file through C#, and the relevent part looks like this

<ss:Cell>
    <ss:Data ss:Type="DateTime">2009-01-18T00:00:00.000</ss:Data>
</ss:Cell>

However it displays in excel as a number , like 41249 that bears no resemblence to the original date. If I right click the cell, and change the format to dd/MM/yyyy or whatever, then it displays the correct date. Anyone know how I can set the format in the XML for how the date is displayed? The data is there, it's just being displayed incorrectly.

Answer 1

In the <Styles> section add

  <Style ss:ID="s22">
   <NumberFormat ss:Format="yyyy\-mm\-dd"/>
  </Style>

then mark your cell with the style:

<Cell ss:StyleID="s22"><Data ss:Type="DateTime">2009-01-21T00:00:00.000</Data></Cell>

Alternatively, open your XML file in Excel, make the change you want, re-save it as XML, then look to see what changed.