Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reading Datetime value From Excel sheet

when am trying to read datetime type value from excel sheet it is returning a double value.for example if want to read value '2007-02-19 14:11:45.730' like this, i am getting a double type value .further i am converting this double value using timespan,but not complete successfully because i am getting only this value '2007-02-19 12:00:00 AM'
now i want exact same datetime value as first one. My code is like :-

TimeSpan datefromexcel = new TimeSpan(Convert.ToInt32((range.Cells[rCnt, cCnt] as Excel.Range).Value2), 0, 0, 0);    DateTime inputdate = new DateTime(1900, 1, 1).Add(datefromexcel);     arrrow2[cCnt - 1] = inputdate.ToString(); 

Please help!!! Thanks.

like image 643
Pranav Avatar asked Dec 27 '10 11:12

Pranav


People also ask

How do I get the DateTime value in Excel?

The Excel DATEVALUE function converts a date represented as a text string into a valid Excel date. For example, the formula =DATEVALUE("3/10/1975") returns a serial number (... The Excel LEFT function extracts a given number of characters from the left side of a supplied text string.

How to read date field from Excel in c#?

Cells[rCnt, cCnt] as Excel. Range). Value2), 0, 0, 0); DateTime inputdate = new DateTime(1900, 1, 1). Add(datefromexcel); arrrow2[cCnt - 1] = inputdate.

What is DateTime in Excel?

The underlying data type of a datetime in Excel is a 64-bit floating point number where the length of a day equals 1 and 1st Jan 1900 00:00 equals 1 . So 11th June 2009 17:30 is about 39975.72917 .


1 Answers

You need to convert the date format from OLE Automation to the .net format by using DateTime.FromOADate.

double d = double.Parse(b); DateTime conv = DateTime.FromOADate(d); 
like image 131
Mikael Svenson Avatar answered Sep 16 '22 21:09

Mikael Svenson