Milliseconds missing when getting a DateTime value from Excel using .Net Interop

Question

If I put a DateTime value into an Excel cell using Range.set_value through .Net COM Interop, and then retrieve the value of that same cell using Range.get_value, the Millisecond part of the value is not returned, though everything else is correct.

Is this a bug?

What is the workaround? I'm guessing that using the Value2 property instead might help. Has anybody else tried this?

Answer 1

If you set a date/time with a millisecond value in Excel manually, does it maintain it? I don't know about the Excel internal object model, but it's conceivable that it just doesn't support milliseconds.

EDIT: Okay, now we know that the set fails (the get may also fail, of course)... you could try setting it as a double after converting it via DateTime.ToOADate(). I'm not saying I'm holding out much hope, but it's worth a try...

Answer 2

As Jon suggested, converting the DateTime to a double using DateTime.ToOADate (then back again using DateTime.FromOADate) works if you set the value using the Range.Value2 property.

The only trouble with this property is that, if you don't already know, it doesn't tell you that the cell is supposed to be treated as a DateTime. I guess to solve this, you'd need to use a two-pass approach: get cell values using Range.get_Value to determine their type, then, for any DateTime cells, get their values again using Range.Value2, then convert using DateTime.FromOADate.

Answer 3

It's probably because of the issue described in this KB article.

In which case, setting the Value2 property should work.