Getting time values from an Excel sheet

Question

      Microsoft.Office.Interop.Excel.Application app = 
                                new Microsoft.Office.Interop.Excel.Application();

      Workbook wb = app.Workbooks.Open(fname);
      Worksheet ws = wb.Worksheets[1];

      // read some data                    
      var Monday = ws.get_Range("D11");
      var Tuesday = ws.get_Range("D13");

I am using Microsoft.Office.Interop.Excelto retrieve hours worked from an Excel sheet. My code seems to be working properly however I keep getting the values COM Object for Monday and Tuesday.

Why is it not returning the actual values in cell D11 and D13 from my spreadsheet??

I am also getting the same COM Object values for ws and wb, not sure if this has any relevance thought I would just throw that out there.

Is there perhaps a function in D11? Try adding .value to your get_range — crthompson
@paqogomez: Yes there is. I did what you said and it worked, except it returned a value of 0.4 something and the value in the cell is 11 hours so im not sure how its getting the 0.4 now — teepee
Time is held as a (Seconds since midnight)/(Seconds in day). Therefore 11 hours is 11/24 which is .458. — Tony Dallimore
As an aside, the date is held as days since 31 December 1899. So if a cell holds a date and time, the integer part defines the date and the fraction defines the time. Here in the UK it is 14 May 2014 00:18 which is held as 41773.0129. — Tony Dallimore
or if you just want the string representation use ws.get_Range("D11").Text; — user2140173

alex.pulver alex.pulver · Accepted Answer · 2014-05-14T08:37:10

MS Excel stores the dates as float values. The integer part represents the days and the fractional part keeps the hours, minutes and seconds.

Check this code that extracts the hours and also the minutes and seconds, maybe you need them:

float excelValue = 0.4f;

int miliseconds = (int)Math.Round(excelValue*86400000);
int hour = miliseconds/( 60/*minutes*/*60/*seconds*/*1000 );
miliseconds = miliseconds - hour*60/*minutes*/*60/*seconds*/*1000;
int minutes = miliseconds/( 60/*seconds*/*1000 );
miliseconds = miliseconds - minutes*60/*seconds*/*1000;
int seconds = miliseconds/1000;

Getting time values from an Excel sheet

1 Answers