C# Parsing Excel custom cell format

Question

I am parsing an Excel file and am having difficulty reading in the value of a cell that has a custom cell format of dd mmm yy. The value in the cell in question is 29 Oct 09 (cell B25). When I run

String arrive = Convert.ToString(_worksheets["GENERAL"].get_Range("B25", Type.Missing).Value2);

I get "40114" as the cell's value.

Then when I try

DateTime arrive = Convert.ToDateTime(_worksheets["GENERAL"].get_Range("B25", Type.Missing).Value2);

I get an error stating "When casting from a number the value must be a number less than infinity."

Any thoughts ideas would be greatly appreciated.

Thanks.

Answer 1

Seems like you want the DateTime.ParseExact method, since this is a non-standard format you're working with, as far as .NET is concerned.

Try the following:

var dateTimeStr = _worksheets["GENERAL"].get_Range("B25", Type.Missing).Value2);
var dateTime = DateTime.ParseExact(dateTimeStr, "dd MMM yy");

The MSDN Custom Date and Time Frormat Strings page may also be a good reference to bookmark.

Answer 2

That random number is an OLE date.

You probably want the DateTime.FromOADate method. Have a look at the reference on MSDN.