Dot Net Perls

Problem

Use FromOADate to convert Excel dates from interop code to DateTime values. Every developer who needs to use Excel with C# or other languages will encounter dates. However, these dates are stored as large integers (doubles), and are completely unreadable and unusable in that form. We need to convert OLE Automation Dates to real dates.

Solution: C#

We must use the DateTime method called FromOADate. This will convert those 5-digit integers (doubles) on the left side of the above table to the regular DateTimes on the right. The code that follows shows some example syntax, and it also proves the correct result.

/// <summary>
/// Example class for showing the FromOADate method.
/// </summary>
class FromExcelDate
{
    /// <summary>
    /// Example method that runs the tests.
    /// </summary>
    public FromExcelDate()
    {
        var stringArray = new string[]
        {
            // OA Dates in early 2008.
            "39456", // (1/9/2008)
            "39482",
            "39484",
            "39493",
            "39496",
            "39497",
            "39501",
            "39506",
            "39510",
            "39513",
            "39515",
            "39521", // (3/14/2008)
            // Different series starts here.
            "36526", // (1/1/2000)
            "36557", // (2/1/2000)
            "39270" // (7//7/2007)
        };

        foreach (string badDate in stringArray)
        {
            // We must have a double to convert the OA date to a real date.
            double oaDouble = double.Parse(badDate);

            // Get the converted date from the OLE automation date.
            DateTime convertedDate = DateTime.FromOADate(oaDouble);

            // Use Debug.WriteLine for debugging.
            System.Diagnostics.Debug.WriteLine(badDate + " = " +
                convertedDate.ToShortDateString());
        }
    }
}

• Sample array declared
  Above, the stringArray (with the type var) is simply an example of OADates you might encounter. Typically the doubles will be somewhere in the 30,000's, but you may encounter outliers.
• Loops through each example string
  We use a foreach loop to look at each "bad date" in the array. These bad dates would be typically be extracted from Microsoft Excel Interop or some databases.
• Must be double type
  The FromOADate method requires that it receive a double value. For the custom code above, we convert the string to a double. You may not need to do this.
• FromOADate is static
  Finally, we use the static DateTime.FromOADate to turn the double into a regular DateTime.

Some example output

Just to make sure my code works, and provide another example for you to look at, I want to show some sample output from the above class. The important thing to take from this article is what FromOADate can be used for, and how to use it.

39456 = 1/9/2008
39482 = 2/4/2008
39484 = 2/6/2008
39493 = 2/15/2008
39496 = 2/18/2008
39497 = 2/19/2008
39501 = 2/23/2008
39506 = 2/28/2008
39510 = 3/3/2008
39513 = 3/6/2008
39515 = 3/8/2008
39521 = 3/14/2008
36526 = 1/1/2000
36557 = 2/1/2000
39270 = 7/7/2007

Conclusion

Download and view this code at my code archive. This is a tricky topic and this article serves as an adjunct to my other Excel Interop article. These methods are mainly required for legacy XLS files, which may be replaced with more robust XLSX XML files (we can hope).

Ads by The Lounge