Capturing Time Values from an Excel Cell

Question

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 1

When dealing with Excel dates, the date can either be stored as a string representation of a date, or it may be an OA date (OLE Automation Date). I've found that checking for both types is the safest route when parsing Excel dates.

Here's an extension method I wrote for the conversion:

/// <summary>
/// Sometimes the date from Excel is a string, other times it is an OA Date:
/// Excel stores date values as a Double representing the number of days from January 1, 1900.
/// Need to use the FromOADate method which takes a Double and converts to a Date.
/// OA = OLE Automation compatible.
/// </summary>
/// <param name="date">a string to parse into a date</param>
/// <returns>a DateTime value; if the string could not be parsed, returns DateTime.MinValue</returns>
public static DateTime ParseExcelDate( this string date )
{
    DateTime dt;
    if( DateTime.TryParse( date, out dt ) )
    {
        return dt;
    }

    double oaDate;
    if( double.TryParse( date, out oaDate ) )
    {
        return DateTime.FromOADate( oaDate );
    }

    return DateTime.MinValue;
}

In your example, the usage would be:

TimeSpan time = f11Value.ParseExcelDate().TimeOfDay;