CODEWITHSUNDEEP
c#exceldatetime
toosensitive
toosensitive

Reputation: 2375

DateTime.FromOADate returns different date from the Date in Excel

In an Excel cell, I put 12, if I format it as Date, then it is 1/12/1900. In C#, I use DateTime.FromOADate(12), it returns 1/11/1900. but if I put 411 in Excel and format it as date, it will be 2/14/1901. In C#, DateTime.FromOADate(411) returns 2/14/1901, too. I am confused about the discrepancy. How can I get the right Date in C# then?

Upvotes: 3

Views: 1569

Answers (1)

Govert
Govert

Reputation: 16907

This is an Excel quirk where it emulates a Lotus 1-2-3 bug for compatibility. The year 1900 was not a leap year, but Excel treats it as a leap year to be compatible with the Lotus 1-2-3 bug. The OLE date/time processing correctly does not.

There is this amusing anecdote about the issue: http://www.joelonsoftware.com/items/2006/06/16.html

If you need to work around this and emulate the Excel behaviour before 1 March 1900, you can set a double instead of a date, and do a DateTime to double conversion that respects the Excel bug too. Internally Excel always represents the dates as doubles anyway.

Upvotes: 5

Related Questions