Return value from UDF is not recognized as Date
Tane Yoroshi Wahyunadi
Govert van Drimmelen
Excel does not have a date/time type built in. What is available is a double value that has specific formatting applied to it, then displaying as a date.
From an .xll UDF we cannot return an indication to Excel that a number is meant to be formatted as a date/time. So the best you can do is to return the right double value, and then have some other means for settings the formatting. If you return a DateTime from your Excel-DNA function, the right double will be returned, but the formatting is not dealt with. As you’ve found, if you return a string, even reformatting the cell will not help – Excel will not interpret or display your string as a date/time.
On the other hand, if you are using the COM API to set values to the sheet, Excel does update the formatting for cells getting date/time values. So that works a bit better than the UDF case.
For things to work right from a function you pretty much have to return the DateTime from the function. Since it sounds like you are already updating the sheet from outside the calculation model, you might use that opportunity to format the target cell too.
Otherwise the user must know to manually format the cells appropriately, before or after the function call.
-Govert
From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Tane Yoroshi Wahyunadi
Sent: 13 April 2022 14:30
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Return value from UDF is not recognized as Date
Hi,
I have UDF which return short date string in ISO 8601 format (yyyy-MM-dd).
The return value is displayed on cell A1.
While the rest data is written using Worksheet.Range.Value inside ExcelAsyncUtil.QueueAsMacro delegate.
The problem is why date on A1 is not recognized/formatted as date, while the other data is recognized correctly. We can see that A1 has left alignment, and the rest has right alignment. If I change the A column format to long date, A1 is not formatted correctly.
Do you know how to make return value from UDF recognized as date? Is it limitation or correct behavior?
Thank you.
Regards,
Tane
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4caa13e9-01f6-466e-aeea-c1c51e141227n%40googlegroups.com.
Tane Yoroshi Wahyunadi
Tane