2

I understand the basic problem is that Excel represents 1900-01-01 as the value of 1. However, in Excel it's possible to manually type in values pre-1900.

// this converts the DataTable to an Object[,]
DataTable table = ...
Object[,] arr = DataTableUtils.toObjectArray(table, false);

Range r00 = r0.get_Resize(arr.GetLength(0), arr.GetLength(1));
r00.Value = arr; // <-- this will crash if ...

If the arr[,] contains a DateTime object pre 1899-12-31 then a 0x800A03EC exception will result. Oddly enough, 1899-12-31 is fine, Excel adds one day to the value. Originally I replaced pre 1900 dates with 1900-01-01, but in the output file, they were 1900-01-02!

For now, I'm living with the workaround solution to replace the bad DateTime values. However, if someone has a better solution then please let me know.

Loathing
  • 5,109
  • 3
  • 24
  • 35
  • What kind of manipulations are you doing to the DateTime objects? If it's necessary to store them as such, then you may want to create a custom solution to handle all the dates you work with rather than a workaround (not sure what you implemented, though). [This site has some details](http://www.exceluser.com/explore/earlydates.htm). The link also describes how in VBA date serial number 1 represents 1 day earlier (Dec 31, 1899) due to how Lotus handled leap years. It also appears that VBA can handle negative serial numbers, though worksheet functions won't. – Zairja May 09 '12 at 13:00
  • 1
    No manipulation. This problem came from doing database queries on tables with old dates or bad data and then saving the results in XLS format. I think I settled on saving the specific pre-1900 values as text. – Loathing Jun 07 '14 at 00:16

0 Answers0