How to read in DateTime in Excel Interop C#

Question

Possible Duplicate:
Reading Datetime value From Excel sheet

I'm trying to read in a DateTime value from an Excel spreadsheet using Interop in C#. I have all times in the 'C' column of my sheet. My code is as follows:

    public void addTime(Microsoft.Office.Interop.Excel.Workbook workbook)
    {

        Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item("Time Series");
        Excel.Range range = ws.UsedRange;
        int num = 0;
        for (int row = 1; row <= range.Rows.Count; row++ )
        {
            String dtString = ((Excel.Range)ws.Cells[row, "C"]).Value2.ToString();
            DateTime dt = Convert.ToDateTime(dtString);

            this.addEdgeInstance(dt);
        }
    }

Yet this doesn't read in the time. Reading of the string works, but the Convert function does not work. Do I have read in values a different way? Can the values vary? (IE: can I enter '11/11' or '11/11/2011' as Excel recognizes both of these entries as valie DateTime values when working in Excel?)

Can you give an example of what dtString is before it is converted? — Justin Self, Nov 25 '11 at 22:00
Any valid Date/time format that Excel accepts. For example: 11/25/2011 5:47:47 PM or 11/11/2011 or 11/11 etc.... — WildBill, Nov 25 '11 at 22:52

score 9 · Accepted Answer · answered Feb 24 '12 at 16:20

There yo go bud. I modified the code for you a little to reflect the usage of the helper conversion function. The conversion happens in this line: DateTime dt = DateTime.Parse(ConvertToDateTime(dtString)); Feel free to modify the helper function as needed to return a DateTime variable instead of a string, but this is essentially the same thing. Hope this helps!

public void addTime(Microsoft.Office.Interop.Excel.Workbook workbook)  
{  
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item("Time Series");  
Excel.Range range = ws.UsedRange;  
int num = 0;  
for (int row = 1; row <= range.Rows.Count; row++ )  
{  
    String dtString = ((Excel.Range)ws.Cells[row, "C"]).Value2.ToString();  
    DateTime dt = DateTime.Parse(ConvertToDateTime(dtString));  

    this.addEdgeInstance(dt);  
}  
}  


public static string ConvertToDateTime(string strExcelDate)
{
    double excelDate;
    try
    {
        excelDate = Convert.ToDouble(strExcelDate);
    }
    catch
    {
        return strExcelDate;
    }
    if (excelDate < 1)
    {
        throw new ArgumentException("Excel dates cannot be smaller than 0.");
    }
    DateTime dateOfReference = new DateTime(1900, 1, 1);
    if (excelDate > 60d)
    {
        excelDate = excelDate - 2;
    }
    else
    {
        excelDate = excelDate - 1;
    }
    return dateOfReference.AddDays(excelDate).ToShortDateString();
}

How to read in DateTime in Excel Interop C#

1 Answers1