How to read Date value into a DateTime datatype

Apr 27, 2011 at 10:20 PM

I am trying to read a date value in Excel into a DateTime datatype in C#.  I am not sure how to take the Cell value (which returns as a number) and convert it properly to the correct date.

In Excel I have a cell value as 1-Oct-2013 with the Cell format as Custom -> d-mmm-yyyy.

When I look at the Cell[row,col].Styles.NumberFormat.Format value in debugger I see "\\ d\\-mmm\\-yyyy", which is what I expected.

When I try to DateTime.Parse the Cell[row,col].Value.ToString() on any date value including the one above, I get the result 1/01/0001 12:00:00AM.  I was expecting 10/1/2013 12:00:00AM ( the date as found in Excel).

Here is my code (I am looping through every cell from start to finish and putting each row's values in a List<string>):


 using (ExcelPackage package = new ExcelPackage(_currentFile))
    ExcelWorksheet worksheet = package.Workbook.Worksheets[worksheetIndex];
    for (rowIndex = startAtRow; rowIndex < totalRows; rowIndex++)
         List<string> record = new List<string>();
         //read whole row
         for (int columnIndex = 1; columnIndex <= totalColumns; columnIndex++)
               string format = worksheet.Cells[rowIndex, columnIndex].Style.Numberformat.Format;
               if ( format != "General" && !format.Contains("#") && !format.Contains(".") && !format.Contains("0"))   //it's a custom date in this file
                   DateTime result;
                   DateTime.TryParse(worksheet.Cells[rowIndex, columnIndex].Value.ToString(),out result);   //doesn't give the right answer
               }else if (worksheet.Cells[rowIndex, columnIndex].Value != null)
                   record.Add(worksheet.Cells[rowIndex, columnIndex].Value.ToString());
                   record.Add("");  //add blank string for null cell value


Also, if there is a better way to determine when the format indicates a date type, please let me know, the way I have it now is not coded very well I'm sure.



Apr 27, 2011 at 10:39 PM

Answer to the first question:

long serialDate = long.Parse(worksheet.Cells[row, column].Value.ToString());
DateTime result = DateTime.FromOADate(serialDate);

Still would like the best way to determine when the Excel cell format indicates a date type value for my conditional statements.



Apr 29, 2011 at 7:45 AM

Dates are stored as numbers in Excel and the only way to determine the datatype is the numberformat.

EPPlus will convert any build-in date format to a DateTime value, when the file is loaded. The build in formats are...

ID Format

14  mm-dd-yy
15  d-mmm-yy
16  d-mmm
17  mmm-yy
18  h:mm AM/PM
19  h:mm:ss AM/PM
20  h:mm
21  h:mm:ss
22  m/d/yy h:mm
45  mm:ss
46  [h]:mm:ss
47  mmss.0

Custom date formats will be doubles and require the FromOADate conversion you use above.


Feb 4 at 12:38 PM