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
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
41507.25 = August 21, 2013 @ 6:00 am
Refer to the MSDN docs on this function for more information:
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.