Convert time-formatted column in excel to C# DateTime

Question

I am currently using the Excel C# libraries (Microsoft.Office.Interop.Excel) to read an excel spreadsheet into my C# application.

I initially tried to read all the cells as their raw data, but found that Date-formatted cells were giving me a 5-digit integer, and time-formatted cells were returning a decimal. So I then found out that you can use a date-conversion method built into Excel's C# library, like so:

DateTime excelDate = (DateTime)ExcelCalcValue.ExcelDateToDateTime(workbook, Double.Parse(cell.Value.ToString()));
output = excelDate.ToString("yyyy-MM-dd HH:mm");

Through debugging my application with various test sheets, I have been able to record the various format strings that cells return when they are formatted in different ways. These are below:

(WorksheetCell.CellFormat.FormatString)

Times

[$-F400]h:mm:ss\\ AM/PM
hh:mm:ss;@
h:mm:ss;@
[$-409]hh:mm:ss\\ AM/PM;@
[$-409]h:mm:ss\\ AM/PM;@

Dates

m/d/yy
[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy
dd/mm/yyyy;@
dd/mm/yy;@
d/m/yy;@
d\\.m\\.yy;@
yyyy\\-mm\\-dd;@
[$-809]dd\\ mmmm\\ yyyy;@
[$-809]d\\ mmmm\\ yyyy;@

Using these, I can now reliably determine the formatting style of a cell in excel. Using the earlier code, I can detect a date-formatted cell and return the proper data in DateTime format. However, I cannot see an equivalent function for converting time-formatted cells.

I get a result of 0.58368055555555554 when I read a cell time-formatted as [$-F400]h:mm:ss\\ AM/PM. I have absolutely no idea how to convert this into a DateTime, or indeed what this float represents.

Can anyone suggest a method of converting time-formatted excel cells (which are stored as a strange float) into the correct DateTime variable?

Answer 1

As FrankPI said, use DateTime.FromOADate(). You would use this function with the raw data from an Excel cell - there is no need to parse the format.

Excel encodes its dates and times in a double. The integral portion represents the days after January 1, 1900. The fraction part represents the time since midnight of the day referenced. For example:

1.5 is January 1, 1900 @ Noon

and

41507.25 = August 21, 2013 @ 6:00 am

Refer to the MSDN docs on this function for more information:

http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx

Answer 2

The "strange float" can probably be converted too a DateTime via the DateTime.FromOADate() method. Actually, it is the number of days since January, 1, 1900 with the time as fractions, e. g. 0.04236 = 1/24 + 1/(24 * 60) for 1:01 am.