Datetime format in Excel Function returns 12/30/1899

Jan 18, 2013 at 3:07 PM

I have this function:

[ExcelFunction(IsExceptionSafe = true)]   

public static object GetData([ExcelArgument("ID")] string CID,                                     [ExcelArgument("Date in the format of mm/dd/yyyy")] DateTime date, .....

The issue is the following:

The value inserted in the date input box using Insert Formula in Excel gives a double value, less than zero and gets converted to 12/30/1899 no matter what value i have inserted in the input box.

DateTime.FromOADate doesn't work cuz the value i get from excel is <0

Any help?

Coordinator
Jan 18, 2013 at 5:17 PM

Hi,

For parameters of type DateTime, Excel-DNA registers the function as taking a double, and then converts the double to a DateTime (Excel has no DateTime data type - it always uses doubles.)

It don't know what string formats Excel is able to recognize as dates, but on my machine entering "12/30/2012" is not recognized by Excel as a date, while "12 Dec 2012" is recognized fine. I guess this might depend on you localized version.

So I can think of some options:

  1. Let the users enter the date in a format that Excel recognizes, e.g. "12 Dec 2012" or "2012/12/30", or 
  2. Check your parameter type to a string, and deal with the parsing from string -> DateTime in the function yourself.

-Govert

Jan 22, 2013 at 9:49 AM

Thanks for ur quick reply, it worked fine with "12 Dec 2012" as a string argument.