CODEWITHSUNDEEP
c#exceldate-formatting
Mihai Bratulescu
Mihai Bratulescu

Reputation: 1945

Reading excel in C# gives me an unknown date format

While reading the file and showing it in a DataGridView the date is shown as a number

03/25/2014 00:00 is 41723

03/25/2014 01:00 is 41723,041667

What format is this and how do I find the date/month/year/hour it represents?

string src = "the path";
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + src +
                                                    "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");

OleDbDataAdapter da = new OleDbDataAdapter("select * from [RENNES4_wk13$]", con);

da.Fill(dataSet);
dataGridView1.DataSource = dataSet.Tables[0];

Upvotes: 0

Views: 375

Answers (2)

Shubu
Shubu

Reputation: 11

I faced the same problem, here's a tip... Before opening the file using OLEDB, release your excel file from any running thread or if you have opened it using excel services. I learned that excel output format, for fields like date(in case of custom type), differs when they are already opened in excel services and parallely read using OLEDB. Just give it a try.

Upvotes: 1

Axel Kemper
Axel Kemper

Reputation: 11322

You can convert the number into a DateTime in the following way:

//  cell as number (days since 1.1.1900 00:00)
double x = double.Parse(v);
DateTime d = DateTime.Parse("01.01.1900 00:00");
d = d.AddDays(Math.Floor(x) - 2);   //  had to subtract 2 to get it right. Why?
d = d.AddMinutes((x - Math.Floor(x)) * 24 * 60);

The integer part of the number gives you the days since 01-Jan-1900. The time is given as fraction of a day.

In your example, one hour is 1/24 of a day: 0.041667

Upvotes: 2

Related Questions