Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 4. September 2008 18:19

 [Edit, Sept 5, 2008: nothing incorrect in the following post; however, if I had Google'd first, I would have found that DateTime date = DateTime.FromOADate(d), where d is a double, does exactly the job...]

The project I am currently working on requires reading some data from an Excel workbook into a .NET calculation engine written in C#. Most of my reads follow this pattern: read a named range into an array of objects, then convert the object to the appropriate .NET type.

public static object[,] GetRangeAsArray(Excel.Worksheet sheet, string rangeName)
{
    Excel.Range range = sheet.get_Range(rangeName, Missing.Value);
    object[,] rangeAsArray = range.Value2 as object[,];
    return rangeAsArray;
}

However, I ran into an issue reading dates. Excel stores dates as doubles, which encode the number of days elapsed since January 0, 1900 (Yes, January 0). As a result, the object stored in the array is a double, and the Convert.ToDateTime(double) method throws an InvalidCastExpression, so standard conversion doesn’t work.
If you look a bit deeper into it (here is a very comprehensive page on the topic), you will discover some interesting idiosyncrasies of the date encoding in Excel. For instance, back in the days, the Excel team knowingly implemented a bug to replicate a known bug of Lotus, for the sake of backwards compatibility.
Here is the quick method I wrote to perform that conversion, addressing these issues:

public static DateTime ConvertToDateTime(double excelDate)
{
    if (excelDate < 1)
    {
        throw new ArgumentException("Excel dates cannot be smaller than 0.");
    }
    DateTime dateOfReference = new DateTime(1900, 1, 1);
    if (excelDate > 60d)
    {
        excelDate = excelDate - 2;
    }
    else
    {
        excelDate = excelDate - 1;
    }
    return dateOfReference.AddDays(excelDate);
}

The exercise was interesting to me, because it was a perfect case to try out the [RowTest] and [Row] attributes which now ship with NUnit.
The classic NUnit version of the test would look something like this:

[Test]
public void ConvertExcelDateToDateTimeClassic()
{
    double excelDate = 1.00;
    DateTime expectedDate = new DateTime(1900, 1, 1);
    DateTime date = ExcelTools.ConvertToDateTime(excelDate);
    Assert.AreEqual(expectedDate, date);
}

However, in order to replicate the multiple border cases, you would have to write the same test over and over again. [RowTest] allows to do this in a very compact form, with this syntax:

[RowTest]
[Row(1.0, 1900, 1, 1)]
[Row(59.0, 1900, 2, 28)]
[Row(60.0, 1900, 3, 1)]
[Row(61.0, 1900, 3, 1)]
[Row(36526.0, 2000, 1, 1)]
[Row(401769.0, 3000, 1, 1)]
public void ConvertExcelDateToSimpleDateTime(double excelDate, int year, int month, int day)
{
    DateTime expectedDate = new DateTime(year, month, day);
    DateTime date = ExcelTools.ConvertToDateTime(excelDate);
    Assert.AreEqual(expectedDate, date);
}

I struggled a bit with getting it to work initially, because it seemed that I was missing a reference and the attributes were not recognized. Thanks to Donn Felker for the walkthrough on what to include to "make it work"!

Comments

2/2/2010 12:09:12 PM #

Kartono wahid iqbal

Thank you. It's very useful . I've been helped

Kartono wahid iqbal United States | Reply

6/3/2010 10:17:01 AM #

pingback

Pingback from blog.bansheetechnologies.co.uk

Converting Excel date format into System.DateTime «  Banshee Technologies

blog.bansheetechnologies.co.uk | Reply

6/24/2011 5:20:00 PM #

pingback

Pingback from news1.showbizchat.net

Photos | 2011 Billboard Music Awards Red Carpet | Showbiz News

news1.showbizchat.net | Reply

2/21/2012 8:42:26 PM #

Douglas

Cool. But you did not consider  "Use 1904 data system".

Douglas People's Republic of China | Reply

2/22/2012 2:15:12 PM #

Mathias

How would you go about that? And does it behave differently from DateTime.FromOADate(d)?
Mathias

Mathias United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS