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.

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);

Perhaps you could try using the DateTime.FromOADate method to convert between Excel and .net.

Reading Datetime value From Excel sheet : Try this will be work.

string sDate = (xlRange.Cells[4, 3] as Excel.Range).Value2.ToString();
 
double date = double.Parse(sDate);
 
var dateTime = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");

  

Or you can simply use OleDbDataAdapter to get data from Excel

Alternatively, if your cell is already a real date, just use .Value instead of .Value2:

excelApp.Range[namedRange].Value
{21/02/2013 00:00:00}
    Date: {21/02/2013 00:00:00}
    Day: 21
    DayOfWeek: Thursday
    DayOfYear: 52
    Hour: 0
    Kind: Unspecified
    Millisecond: 0
    Minute: 0
    Month: 2
    Second: 0
    Ticks: 634970016000000000
    TimeOfDay: {00:00:00}
    Year: 2013
	
excelApp.Range[namedRange].Value2
41326.0

i had a similar situation and i used the below code for getting this worked..

Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.CSV);

Workbook workbook = new Workbook(fstream, loadOptions);

Worksheet worksheet = workbook.Worksheets[0];

dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDisplayRange.RowCount, worksheet.Cells.MaxDisplayRange.ColumnCount, true);

DataTable dtCloned = dt.Clone();
ArrayList myAL = new ArrayList();

foreach (DataColumn column in dtCloned.Columns)
{
	if (column.DataType == Type.GetType("System.DateTime"))
	{
		column.DataType = typeof(String);
		myAL.Add(column.ColumnName);
	}
}


foreach (DataRow row in dt.Rows)
{
	dtCloned.ImportRow(row);
}



foreach (string colName in myAL)
{
	dtCloned.Columns[colName].Convert(val => DateTime.Parse(Convert.ToString(val)).ToString("MMMM dd, yyyy"));
}


/*******************************/

public static class MyExtension
{
    public static void Convert<T>(this DataColumn column, Func<object, T> conversion)
    {
        foreach (DataRow row in column.Table.Rows)
        {
            row[column] = conversion(row[column]);
        }
    }
}

Hope this helps some1 thx_joxin

You may want to try out simple function I posted on another thread related to reading date value from excel sheet.

It simply takes text from the cell as input and gives DateTime as output.

I would be happy to see improvement in my sample code provided for benefit of the .Net development community.

Here is the link for the thread https://stackoverflow.com/questions/17927250/c-sharp-not-reading-excel-date-from-spreadsheet/25501735#25501735

Another option: when cell type is unknown at compile time and cell is formatted as Date Range.Value returns a desired DateTime object.

public static DateTime? GetAsDateTimeOrDefault(Range cell)
{
    object cellValue = cell.Value;
    if (cellValue is DateTime result)
    {
        return result;
    }
    return null;
}