I am trying to pull information out of an Excel spreadsheet. The cells I am having problems with have a custom format of h:mm so when I retrieve the value I end up getting a value that does not match what is displayed on the spreadsheet.

I have tried to pull the value from the cell in two different ways.

The first way ends up giving me a value of .027777777778 when it should be :40 (40 minutes).

Dim oWorksheet As Excel.Worksheet

oWorksheet = oWorkBook.Worksheets("Monday")
strDuration = oWorksheet.Range("A1:F15").Cells(4, 3).Value

The second way ends up giving me a value of 12/30/1899 in the column when it should be :40. It returns the same value of 12/30/1899 no matter what value is displayed on the spreadsheet.

OleDbConnection ExcelConn = new OleDbConnection();
ExcelConn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; " +
			     "data source="C:\Timesheet\abc.xls; " + 
                             "Extended Properties=Excel 8.0;";
				
OleDbDataAdapter da;
da = new OleDbDataAdapter("Select * from [Monday$A1:K23]", ExcelConn);
da.Fill(dsTimesheetDetail, "Monday");
dgResults.SetDataBinding(dsTimesheetDetail, "Monday");

The second way works fine if I am retrieving a text value or a numeric value. It only seems to have a problem with the custom format of h:mm.

I would prefer to use the second method just because it would more than likely me a faster way of extracting the data. Plus Excel automation in C# is not as forgiving as it is in VB.NET and I would like to write the app in C# <s>.

Thanks,

Vic