Converting Double to DateTime?
I have a .CSV file which I am reading into a C# program. In one of the columns, there is a date, but it is in the "general" format, so it shows up in the .CSV as a number. For example: 41172.
How can I convert this number to a date with format dd/mm/yyyy in C#? 41172 is equivalent to 20/09/2012.
Answer
To go from an DateTime in the "Excel Format" to a C# Date Time you can use the DateTime.FromOADate function.
In your example above:
DateTime myDate = DateTime.FromOADate(41172);
To write it out for display in the desired format, use:
myDate.ToString("dd/MM/yyyy");
If you are wondering where the discrepancies in Excel's date handling come from, it's supposed to be on purpose:
When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.
Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.