CODEWITHSUNDEEP
c#excel
Mike Baxter
Mike Baxter

Reputation: 7268

Convert time-formatted column in excel to C# DateTime

I am currently using the Excel C# libraries (Microsoft.Office.Interop.Excel) to read an excel spreadsheet into my C# application.

I initially tried to read all the cells as their raw data, but found that Date-formatted cells were giving me a 5-digit integer, and time-formatted cells were returning a decimal. So I then found out that you can use a date-conversion method built into Excel's C# library, like so:

DateTime excelDate = (DateTime)ExcelCalcValue.ExcelDateToDateTime(workbook, Double.Parse(cell.Value.ToString()));
output = excelDate.ToString("yyyy-MM-dd HH:mm");

Through debugging my application with various test sheets, I have been able to record the various format strings that cells return when they are formatted in different ways. These are below:

(WorksheetCell.CellFormat.FormatString)

Times

[$-F400]h:mm:ss\\ AM/PM
hh:mm:ss;@
h:mm:ss;@
[$-409]hh:mm:ss\\ AM/PM;@
[$-409]h:mm:ss\\ AM/PM;@

Dates

m/d/yy
[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy
dd/mm/yyyy;@
dd/mm/yy;@
d/m/yy;@
d\\.m\\.yy;@
yyyy\\-mm\\-dd;@
[$-809]dd\\ mmmm\\ yyyy;@
[$-809]d\\ mmmm\\ yyyy;@

Using these, I can now reliably determine the formatting style of a cell in excel. Using the earlier code, I can detect a date-formatted cell and return the proper data in DateTime format. However, I cannot see an equivalent function for converting time-formatted cells.

I get a result of 0.58368055555555554 when I read a cell time-formatted as [$-F400]h:mm:ss\\ AM/PM. I have absolutely no idea how to convert this into a DateTime, or indeed what this float represents.

Can anyone suggest a method of converting time-formatted excel cells (which are stored as a strange float) into the correct DateTime variable?

Upvotes: 5

Views: 19846

Answers (4)

Muhammad Bilal
Muhammad Bilal

Reputation: 1152

If you want that either date value is in double format or in date format it converts it to date format then try to use following code. datestringvalue should be your input value.

 DateTime dateNow = DateTime.Now;
 DateTime formatedDate = DateTime.TryParse("datestringvalue", out dateNow) ? Convert.ToDateTime("datestringvalue") : DateTime.FromOADate(Convert.ToDouble("datestringvalue"));

Upvotes: 0

t_plusplus
t_plusplus

Reputation: 4209

I wrote this function to handle a date input from Excel into C#. It handles a number of data type possibilities for a date cell: 

/// <summary>
/// Returns DateTime?
/// Excel dates are double values, and sometimes, they're typical dd/mm/yyyy dates.
/// This function handles both possibilities, and the possibility of a blank date input. 
/// /// 
/// </summary>
/// <param name="inputDate"></param>
/// <returns></returns>
private static DateTime? ResolveExcelDateInput(string inputDate)
{
    double incomingDate = 0;

    DateTime incomingDateDate = new DateTime();

    // If the incoming date is a double type, parse it into DateTime
    if (Double.TryParse(inputDate, out incomingDate))
    {
        return DateTime.FromOADate(incomingDate);
    }

    // IF the incoming date value is a date type, parse it.
    var parseDateResult = DateTime.TryParse(inputDate, out incomingDateDate);

    if(parseDateResult)
    {
        // If the parse is successful return the date
        return incomingDateDate;
    }
    else
    {
        // If the parse isn't successful; check if this a blank value and set to a default value.
        if(string.IsNullOrWhiteSpace(inputDate))
        {
            return new DateTime(1901, 1, 1);
        }
        else
        {
            // Otherwise return null value so that is then handled by the validation logic.
            // log a validation result because inputDate is likely an invalid string value that has nothing to do with dates.
            return null;
        }
    }
}

Upvotes: 0

awudoin
awudoin

Reputation: 649

As FrankPI said, use DateTime.FromOADate(). You would use this function with the raw data from an Excel cell - there is no need to parse the format.

Excel encodes its dates and times in a double. The integral portion represents the days after January 1, 1900. The fraction part represents the time since midnight of the day referenced. For example:

1.5 is January 1, 1900 @ Noon

and

41507.25 = August 21, 2013 @ 6:00 am

Refer to the MSDN docs on this function for more information:

http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx

Upvotes: 15

FrankPl
FrankPl

Reputation: 13315

The "strange float" can probably be converted too a DateTime via the DateTime.FromOADate() method. Actually, it is the number of days since January, 1, 1900 with the time as fractions, e. g. 0.04236 = 1/24 + 1/(24 * 60) for 1:01 am.

Upvotes: 4

Related Questions