We want to return from an Excel custom function dates formatted in the user’s local date format (as configured in their Excel or system settings).
Using context.application.cultureInfo.load(...), we can retrieve the user’s localized short date format via the cultureInfo.datetimeFormat.shortDatePattern property.
However, custom function return types (such as Excel.FormattedNumberCellValue or Excel.DoubleCellValue) do not support numberFormatLocal — they only support the invariant numberFormat, which does not accept localized formats, and Excel.CultureInfo does not expose the invariant short date format - only the localized one.
As a result, we can retrieve a localized format string, but cannot apply it in custom function outputs.
We cannot obtain a suitable invariant format either.
This makes it impossible to return correctly formatted, locale-aware date values from custom functions.
Request for improvement:
either expose the Excel-recognized numberFormat string (e.g., "dd/mm/yyyy") corresponding to the user's default short date format, alongside the localized version (e.g., "jj/mm/aaaa"), in cultureInfo class,
OR allow numberFormatLocal to be specified in custom function return types (e.g., Excel.FormattedNumberCellValue).