Last post Dec 25, 2010 08:33 PM by Lannie
Dec 22, 2010 02:01 AM|Bhaskar Rao.Thalatam|LINK
I am writing C# code to read a sheet from a workbook. A couple of cells in the sheet are of Date type. However, when I check out the xml generated for the sheet, it contains numbers, for e.g. 38018 instead of 1 Feb 2005. As a result my code ends up treating
this as number and I am not able to recreate a date object.
Dec 25, 2010 08:33 PM|Lannie|LINK
Yes.. the hidden headaches of Excel appear now and then.
Dates are really stored a numbers (just like a database does, but different rules).
Open new sheet, enter 12/31/2010 (US culture) or (31/12/2010 European culture) for 31 Dec 20010.
Well. it looks like a date, but press CTRL+~ (CTRL plus TILDE keys) and it toggles to the number 40543,
the total number of days from 00 Jan 1900 with 01 Jan 1900 being the value of 1 day.
Press CTRL+~ again to restore the FORMATTED appearance of the number back to a date looking data.
Often I find the FORMAT is not exported, but the underlying value is exported
when exporting XLS sheets to TEXT or XML... sometimes unpredictably at least for a person with average Excel skills like me.
Now if you move to another CELL and enter '12/31/2010 (Single Quote + 12/31/2010), the date is stored as TEXT
and still looks like a date, now it can be exported to XML and then converted into date format in a database,
applying the date mask 'MM/DD/YYYY' in SQL statement on data type conversion from XML Text to database DATE.