TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

<< Back to C-SHARP

C# FromOADate and Excel Dates

Use the DateTime.FromOADate method, converting numbers like 39456 to a date.
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. We examine the FromOADate method.
First, you can use FromOADate as a static method on the DateTime type. This method receives one double value as its parameter, and the returns a full DateTime. This will convert those five-digit integers to the regular DateTimes.DateTime

Array: 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

FromOADate: We show some example calls and output. With Console.WriteLine, we print the results.

Console
C# program that uses FromOADate 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()); } } } Output 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
Input OLE date: 39456 39482 39484 36526 36557 39270 Output DateTime: 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. This is a tricky topic and this article serves as an adjunct to my other Excel Interop article.

Note: These methods are mainly required for legacy XLS files, which may be replaced with more robust XLSX XML files.

© TheDeveloperBlog.com
The Dev Codes

Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf