CODEWITHSUNDEEP
c#exceldatetype-conversion
Ziba Leah
Ziba Leah

Reputation: 2514

DateTime from XLS to C# conversion failure

I'm working on VS2008 .NET3.5 with a Office 2003 doc (.xls). I'm using Microsoft.Office.Interop.Excel to access to a document.

This works fine for my purpose but fails whenever I try to get a Date.

The way I'm using is the most common:

object date= xlWorkSheet.get_Range("E" + i, "E" + i).Value2;
double doubleDate = double.Parse(date);
DateTime finallyDate = DateTime.FromOADate(doubleDate);

The date I have stored is 01/12/1961 (in Italian means first december and if I open excel it tolds me 1 december 1961).

When I run my app it happens that the value of the double become 15011.0 and when the finallyDate value is 2/4/1941 that's not right!

How can I solve this problem? Is there any way to convert (also manually) that 15011 number?

Thank you!!

Upvotes: 1

Views: 1184

Answers (2)

JMK
JMK

Reputation: 28069

Get the Value property instead of Value2, then you will be able to work with a Date object. You may need to cast it as (DateTime).

What you get instead with Value2 is the floating point value of the Date.

For example, check out my spreadsheet below, where A1 contains a date:

Spreadsheet

Then in Excel, I add my reference to Microsoft.Office.Interop.Excel, and grab the Value and Value2 properties like so:

var excel = new Excel.Application();

var workbook = excel.Workbooks.Open(@"C:\Test\Test.xlsx");
var worksheet = (Excel.Worksheet)workbook.Sheets[1];

Excel.Range range = worksheet.get_Range("A1");

var rangeAsValue = range.Value;
var rangeAsValue2 = range.Value2;

Console.WriteLine(rangeAsValue);
Console.WriteLine(rangeAsValue2);

Console.ReadLine();

I get this output:

Output

Interestingly, if you try this in a .Net 4.5 application, it still works but the type of var is resolved as dynamic for rangeAsValue and rangeAsValue2, and you lose your intellisense.

Upvotes: 3

Guo Hong Lim
Guo Hong Lim

Reputation: 1710

To the first part of your question, How can I solve this problem?

Do ensure

  1. Document is saved.
  2. You are accessing the right cell.

To your second part of the question, Is there any way to convert (also manually) that 15011 number?

The 15011 is actually the number of days from 1-1-1900. I usually just divide the mentioned number by 365 to get a rough date. You will be asking why rough? Because we need to take leap year into consideration when some years have 366 days.

If you need an accurate calculation of the date with the serial number, you might want to use the method found here. http://naspinski.net/post/Translate-an-Excel-Serial-Date-into-a-C-DateTime.aspx

public DateTime ExcelSerialDateToDT(int nSerialDate)
 {
 int l = nSerialDate + 68569 + 2415019;
 int n = ((4 * l) / 146097);
 l = l - ((146097 * n + 3) / 4);
 int i = ((4000 * (l + 1)) / 1461001);
 l = l - ((1461 * i) / 4) + 31;
 int j = ((80 * l) / 2447);
 int nDay = l - ((2447 * j) / 80);
 l = (j / 11);
 int nMonth = j + 2 - (12 * l);
 int nYear = 100 * (n - 49) + i + l;

return DateTime.Parse(nMonth + "/" + nDay + "/" + nYear);
 }

You can read more on why this behavior on Microsoft Office Excel on http://www.codeproject.com/Articles/2750/Excel-serial-date-to-Day-Month-Year-and-vise-versa

Upvotes: 0

Related Questions