Returning a date value

Mar 8, 2011 at 1:05 PM

Hello,

while trying ExcelDNA, I encountered a problem with returning date values from C#.

For example: >> DateTime.Parse("2011-01-21").ToOADate() << returns double '40564' but Excel doesn't set the cell format to 'Date' like other formulas do.

Is there a way to automatically set the format right?

Thanks in advance, great Project :)

Coordinator
Mar 8, 2011 at 9:05 PM

Hi,

Excel internally has no date/time representation of cell values - these are just stored as doubles; what you see on the sheet is just a display format applied to those cells. Some of the built-in functions like NOW() and TODAY() have some magical reformatting hard-coded in Excel, but this feature is not extensible. So Excel does not support automatic reformatting of user-defined functions, whether the functions are written in VBA, the native C API or .NET with Excel-DNA.

The context in which user-defined function are called from Excel also don't allow you to set the cell formatting from your function. If you really wanted to do this, there are some possible hacks - you could start a separate thread from your function that uses the COM interface to reformat the calling cell, or uses the COM interface to call a macro that does the reformatting. You'd need to think through cases like when you function is embedded in a larger formula. I think it can be done, but would need some care to implement reliable and safely.

-Govert

 

Mar 9, 2011 at 9:32 AM
Edited Mar 9, 2011 at 9:45 AM

Thank you for the quick answer Govert,

will try the thread approach.

Best regards

Jun 9, 2014 at 4:37 PM
hi, did anyone come up with some sample code for this, I'd like to manually set the format to Date too (since I'm already launching a separate thread to paste in values)

emulating Bloomberg is hard :(
Coordinator
Jun 9, 2014 at 9:30 PM
Emulating Bloomberg is a bad idea . . .
Jun 10, 2014 at 10:12 AM
very true... but I don't have a choice.