Date Conversion from Excel 2010 through InterOp into VB Studio 2005

Question

I hope this is in the correct forum. If not, please feel free to move it or tell me where I should post it.

I have a Visual Studio 2005 VB program that uses the Office 2010 InterOp libraries to pull data from an Excel Form and insert the data into SQL Server.

Here is the problem.  The Excel data cell has a value of "1/1/1900" and is formated as a Date in '*3/14/2001' Excel format.  The VB program reads the value into a string. When the value is pulled into the string, the value of the string is "12/31/1899" instead of "1/1/1900"

My guess is that Excel saves the value '1/1/1900' as a full date & time of  "1/1/1900 00:00:00" and somwhere in pulling the data from Excel through the interop libraries it takes the time as UTC/GMT. Since we're in Eastern time (EST) that would move the time back five hours which would put the date at 12/31/1899 19:00:00.  Of course this causes a problem because we take the value and insert it into a database record as a smalldatetime (which only goes down to 1/1/1900).

My fix was just to put 1/2/1900 in the form, but this brings up and interesting point of how to deal with a data descrepencey due to conversion between UTC/GMT and the machine time zone.

I'm totally guessing that this convertion is done in the InterOp code, but is this a bug in the InterOp code or is this normal?

I'm a little worried that date fields from other Excel files that we use will have the same problem (although I've heard no complaints yet).

Thanks for listening/reading.

---

Alan Edwards

Answer 1

Hi Alan:

This forum handles requests related to Open Specifications documentation issues.
The Open Specifications can be found at: http://msdn2.microsoft.com/en-us/library/cc203350.aspx.
Your question does not appear to be related to the Open Specifications documentation set.

I suggest you try the following forum to get your question answered.

Excel for Developers

http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

 

---

Regards, Obaid Farooqi

Answer 2

Thank you.  I'll try there.  Maybe the moderator can delete this thread as I am going to post it in the location suggested above.  Thanks! 

---

Alan Edwards