Excel Date column returning INT using EPPlus

I stumbled upon this issue today when trying to generate some Excel documents from some ASP.NET DataTables: I had no problem with strings, but ran into few issues with numeric types (int, doubles, decimals) and DataTables, which were formatted as string or as numeric representations (OADate).

Here's the solution I eventually managed to pull off:

if (dc.DataType == typeof(DateTime))
{
    if (!r.IsNull(dc))
    {
        ws.SetValue(row, col, (DateTime)r[dc]);
        // Change the following line if you need a different DateTime format
        var dtFormat = "dd/MM/yyyy";
        ws.Cells[row, col].Style.Numberformat.Format = dtFormat;
    }
    else ws.SetValue(row, col, null);
}

Apparently, the trick was to set the value as DateTime and then configure the proper Style.Numberformat.Formataccordingly.

I published the full code sample (DataTable to Excel file with EPPlus) in this post on my blog.

...when I need to read that excel file, the only dates that are incorrect are the ones the user has changed

So when you read the modified excel-sheet, the modified dates are numbers whereas the unchanged values are strings in your date-format?

You could get the DateTime via DateTime.FromOADate:

long dateNum = long.Parse(worksheet.Cells[row, column].Value.ToString());
DateTime result = DateTime.FromOADate(dateNum);

With your sample-number:

Console.Write(DateTime.FromOADate(41640)); // ->  01/01/2014