I would like to ask your advice about a problem I'm experiencing with dates.
To cut a long story short I have an xml file like this:
<TABLE>
<ROW>
<CELL format="#,##0.#">15000000000</CELL>
<CELL format="mm/dd/yyyy">5/6/2007 12:00AM</CELL>
</ROW>
<ROW>
<CELL>Value 4</CELL>
<CELL format="mm/dd/yyyy">1/1/08</CELL>
</ROW>
<ROW>
<CELL>Value 3</CELL>
<CELL format="mm/dd/yyyy">4/3/05</CELL>
</ROW>
</TABLE>
this excel is used to generate an Excel file, using the format attribute for the cell Style.Custom property. Everything works fine except for the second cell of the first row: the text is not formatted.
If I delete the "12:00AM" everything works fine also for this line.
I would like also to report the fact that if the format is set to "mm/dd/yyyy hh:mmAM/PM" the AM and PM is displayed as Am or Pm where m is the number of the month.
this fixes the "mm/dd/yyy hh:mmAM/PM" (and this is very cool) but it doesn't solve my "main" problem.
My problem is that I have dates in string format; some of them have the time expressed and some not; I would like them to be all formatted in the same way (mm/dd/yyyy for example).
I would aspect to have three cells containing respectively "05/06/2007", "01/01/2008" and "04/03/2005".
What happens instead is that everything is fine for Cell2 and Cell3 but Cell1 contains "5/6/2007 12:00AM"; the bad thing is that Excel doesn't recognize Cell1 as date so if I try to sort the cells "strange" results are produced (Cell1 is sorted after Cell3 and Cell2 which is obviously not the case).