Capturing Time Values from an Excel Cell

msharpp picture msharpp · Mar 13, 2011 · Viewed 7.2k times · Source

I am writing an Excel app that will read and write specified values from an Excel file, and display them to the user. However, when I try to read from a cell which has a Number Format or a function typed 'hh:min' (Hour:Min) I cannot obtain that value how exactly I want.

Here is my code...

ws[dateTimePicker1.Value.Day + 1].get_Range("F" + i.ToString(), Type.Missing);
    if (range.Value2 != null)  
        val += " - " + range.Value2.ToString();   //Sets FXX to val
    lbHK1.Items.Add(val);

Where...

  • ws = my worksheet
  • dateTimePicker1 = my date time picker which helps me decide which file will be opened
  • i = is an integer that helps me decide Row number of that cell
  • range = is an object created from Microsoft.Office.Interop.Excel.Range

In my example, when i = 11, F11 is the cell that contains the time value which is 06:30 (in Excel, fx : 06:30:00). However, when I try to get that value, it returns a double type such as 0.263888888888889

How can I get the value formatted correctly as it is displayed in Excel, rather than a meaningless double value?

Answer

Charles Williams picture Charles Williams · Mar 13, 2011

Excel stores times internally as doubles containing decimal fractions of a 24-hour day: so 6:30 AM would be 0.2708333