Fetching datetime/user-defined field from Excel?

[languy]

Hi there,

I have a problem retrieving a datetime from an Excel Worksheet. I have tried
to retrieve the date both from a user-defined field (which is the original
format) and from a datetime field. When fetching the data using the Excel
COM api i C# I will get the data as a double and this double is even not the
ticks that corresponds to the time.

-- snip --
private object[] ConvertToArray(System.Array values)
{
object[] theArray = new object[values.Length];
for (int i = 1; i <= values.Length; i++)
{
// when it is a DateTime or User-Defined field in Excel a double is
returned here ???
object obj = values.GetValue(1, i);

theArray[i-1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}

Excel.Workbook theWorkbook = null;
Excel.Application ExcelObj = new Excel.Application();
object mv = Missing.Value;
theWorkbook = ExcelObj.Workbooks.Open(
"c:\\navision_drift.xls", mv, mv, mv,
mv, mv, mv, mv, mv, mv, mv,
mv, mv, mv, mv);

Excel.Range range = worksheet.get_Range("A"+i.ToString(), "H" +
i.ToString());
System.Array myvalues = null;
myvalues = (System.Array)range.Cells.Value2;
object[] strArray = ConvertToArray(myvalues);
-- snip --

Does anyone know how to fetch the datetime field as is and prevent it from
being converted to a double?

I hope for a soon reply on this.

Thanks in regards,
Jess

 

[Bernie Deitrick]

Jess,

Datetime values are Doubles in Excel - they just happen to be formatted as date and time. The
integer portion is the number of days since 1/1/1900, and the decimal part is the fraction of a day
(time since Miodnight). So the double value

38617.3495454861

is today at 8:23 AM.

You can use the TEXT function to convert:

myDateTimeString = ExcelApplicationObject.WorksheetFunction.TEXT(DoubleVAlue, "mm/dd/yyy hh:mm:ss")

HTH,
Bernie
MS Excel MVP

 

[languy]

Hi there,

I did find a way to solve this problem.

I figured out that dates in Excel are based on 1/1/1900, so the # I am
getting is the # of days since then. Thus if I do something like
illustratred in the snip below then it will then give me the correct date as
is.

-- snip --
double dbl = (double)values.GetValue(1, i);
TimeSpan dateFromExcel = new TimeSpan((int)dbl-2,0,0,0);
DateTime resultingDate = new DateTime(1900,1,1).Add(dateFromExcel);
-- snip --

Hth
Jess