Random date format read from Excel

Question

I'm using OleDb to read data from an Excel file. The piece of code that reads the data is the following:

OleDbCommand oleDbCommand = new OleDbCommand(selectCommandText, oleDbConnection);

using (OleDbDataReader dr = oleDbCommand.ExecuteReader())
{
    DataTable dt = new DataTable();
    dt.Load(dr);
    return dt;
}

The problem is that the data readed appears randomly as a string sometimes (for example "16.02.1995") or like a number - timestamp (41187), something like this convert Excel Date Serial Number to Regular Date.

Is there any way to solve this? I would like to read the data always as a format, don't matter if it's a number or a string.

Edit: I found that when I have the Excel file opened the date readed is in number format (date serial number) and when I don't have the file opened the date is in string format. Does somebody know why?

Edit2: The personalized format used in the date cell

Maytham Fahmi · Accepted Answer · 2020-07-02T11:15:09.827

To convert date number or date string to c# you need two different methods.

One to convert string and the other one to convert number to date format.

So, regarding converting string to date, there are TryParse method in c#, and regarding the number conversation to date there are already answer on that in SO.

Putting that together we can do some thing like:

public static DateTime? GetDateTime(object o)
{
    DateTime? date;
    try
    {
        date = FromStringToDate(o.ToString());
        if (date == DateTime.MinValue)
        {
            date = FromExcelSerialDate((int)o);
        }
    }
    catch (Exception e)
    {
        //log your exception
        date = null;
    }

    return date;
}

private static DateTime FromExcelSerialDate(int serialDate)
{
    if (serialDate > 59) serialDate -= 1; //Excel/Lotus 2/29/1900 bug   
    return new DateTime(1899, 12, 31).AddDays(serialDate);
}

private static DateTime FromStringToDate(string stringDate)
{
    DateTime.TryParse(stringDate, out DateTime result);
    return result;
}

To put that in use, in your main method for testing you can do some thing like:

List<object> excelData = new List<object>()
{
    "16.02.1995",
    41187,
    13131.3242,
    "",
    null
};

foreach (object o in excelData)
{
    var dateTime = GetDateTime(o);
    if (dateTime != null)
    {
        Console.WriteLine(dateTime);
    }
}

The output will be:

16-02-1995 00:00:00    
05-10-2012 00:00:00

I have testing it i excel as well.

Note: This is just example, you might improve the methods, change the order, adding more protective lines so it does not break, for example if date is null, empty or wrong format in excel to fit you business logic.

Nice solution but I would like to go further. Which is the reason that makes the data readed appears in string format when the file is closed and on the contrary appears in number format when the file is opened? These formats can change in order to read always in string or number format? Thanks :) — rlm96, Jul 02 '20 at 11:31
are you able to share a simple and tiny sample excel ark that you have issue with, I want to try what you have in thoughts, it just to be sure giving you the right feedback. — Maytham Fahmi, Jul 02 '20 at 11:33
Sure, I was trying to replicate the issue in order to extend it a little bit more but I have noticed that I don't use the default Excel "date" format. I use a personalized one, so using Excel "date" format the readed format is always the same. The problem is realted with the cell format I have used, this seems more congruent — rlm96, Jul 02 '20 at 11:52
Great, would I understand that it is resolved now, did I misunderstand something. Please advice. — Maytham Fahmi, Jul 02 '20 at 12:21

Random date format read from Excel

1 Answers1