Home
C#
DateTime.FromOADate and Excel Dates
This page was last reviewed on Apr 16, 2023.
Dot Net Perls
FromOADate. Excel interop dates are stored as integers. It is possible to convert them to DateTimes. We use FromOADate to convert OLE Automation Dates to real DateTime values.
Method notes. We examine the FromOADate method. OA refers to "OLE Automation" and these are 5-digit numbers that represent a date and time.
First, FromOADate receives one double value as its parameter, and the returns a full DateTime. This will convert those 5-digit integers to the regular DateTimes.
DateTime
Start We see an array of OADates you might encounter. Typically the doubles will be somewhere in the 30,000s, but you may encounter outliers.
Array
Next It loops with foreach through each example string. We use a foreach loop to look at each number in the array.
foreach
Info These numbers would be typically be extracted from Microsoft Excel Interop or some databases.
Excel Interop
Here We show some example calls and output. With Console.WriteLine, we print the results.
Console.WriteLine
using System; class Program { static void Main() { var arr = 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 b in arr) { // We must have a double to convert the OA date to a real date. double d = double.Parse(b); // Get the converted date from the OLE automation date. DateTime conv = DateTime.FromOADate(d); // Write to console. Console.WriteLine("{0} = {1}", b, conv.ToShortDateString()); } } }
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
Values must be double types. 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.
double
Finally We use the static DateTime.FromOADate to turn the double into a regular DateTime.
static
39456 39482 39484 36526 36557 39270
1/9/2008 2/4/2008 2/6/2008 1/1/2000 2/1/2000 7/7/2007
Summary. We used the FromOADate method on the DateTime type in the C# language. We saw the input and output. These methods are often required for legacy XLS files.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Apr 16, 2023 (edit).
Home
Changes
© 2007-2024 Sam Allen.