Freigeben über

Converting Excel Dates to .NET DateTime

  • Artikel

Just when I thought it was safe to write .NET code in Excel, Mohit Gupta and Eric Lippert opened up for me a whole new world of horrible world of dates in Excel.

Mohit recently added code similar to this snippet to our code tree to convert Excel dates into a DateTime or a DateTime into an Excel date.

        static readonly DateTime march1st1900 = new DateTime(1900, 03, 01);
static readonly DateTime december31st1899 = new DateTime(1899, 12, 31);
static readonly DateTime january1st1904 = new DateTime(1904, 01, 01);
static readonly TimeSpan date1904adjustment = new TimeSpan(4 * 365 + 2, 0, 0, 0, 0);
static readonly TimeSpan before1stMarchAdjustment = new TimeSpan(1, 0, 0, 0);
bool date1904 = ActiveWorkbook.Date1904;

object ConvertDateToExcelDate(DateTime date)
{
if (date1904)
{
if (date >= january1st1904)
return date - date1904adjustment;
else
return date.ToString(DateFormat, DateFormatProvider);
}
if (date >= march1st1900)
return date;
if (date < march1st1900 && date > december31st1899)
return date - before1stMarchAdjustment;
return date.ToString(DateFormat, DateFormatProvider);
}

        DateTime ConvertExcelDateToDate(object excelDate)
{
DateTime date = (DateTime)excelDate;
if (Date1904)
return date + date1904adjustment;
if (date < march1st1900)
return date + before1stMarchAdjustment;
return date;
}

There are two complexities that this code takes care of when converting back and forth from an Excel date to a DateTime. First, Excel can represent dates in two formats--the "1900" format or the "1904" format. The 1900 format is based on a system where when converted to a number, it represents the number of elapsed days since January 1, 1900. The 1904 format is based on a system where when converted to a number, it represents the number of elapsed days since January 1, 1904. The 1904 format was introduced by early Macintosh computers because of a problem with the 1900 format that I will describe later.

If an Excel workbook is using the 1904 format, when you convert a date from that workbook into a DateTime directly you will get the wrong value. It will be off by 4 years and 2 leap days since DateTime is expecting the 1900 format where the value of the Excel date represented by a number is the number of elapsed days since January 1, 1900, not January 1, 1904. So this code would give bad date-times if you are using the 1904 format in your workbook.

object excelDate = myRange.get_value(Type.Missing);
DateTime possiblyBadDateIfExcelIsIn1904Mode = (DateTime)excelDate;

To get a 1904 format date into a DateTime format you must add to the 1904 format date 4 years and 2 leap days (to make up for the fact that the 1904 has it's 0 in 1904 rather than 1900). So if you write this code instead and use the function I listed above (ConvertExcelDateToDate), you will get the right result if the 1904 date system is used.

object excelDate = myRange.get_value(Type.Missing);
DateTime goodDate = ConvertExcelDateToDate(excelDate);

But the code I presented also has a correction for 1900 format dates. It turns out that when Lotus 1-2-3 was written, the programmers incorrectly thought that 1900 was a leap year. When Microsoft wrote Excel, they wanted to make sure they kept compatibility with existing Lotus 1-2-3 spreadsheets by making it so that they calculated the number of days elapsed since December 31st, 1899 instead of January 1, 1900. For more information, see Eric Lippert's blog on OLEAUT. When .NET built DateTime, they didn't try to backup to December 31st, 1899--they calculated from January 1, 1900. So to get an Excel date in 1900 format that is before March 1, 1900 into a DateTime properly, you have to add one day.

Finally, Excel can't represent days before January 1, 1900 when in 1900 format or days before January 1, 1904 when in 1904 format. So in those cases where you are converting a DateTime to an Excel date, you have to pass a string instead of a number representing the date--since these dates can't be represented as dates in Excel--only as strings.

Comments