Localized Formatting via FormattedNumberCellValue

[e16n]

Hello, can you please confirm that it is not currently possible to use FormattedNumberCellValue with a localized date format? I cannot find an equivalent to Excel.Range.numberFormatLocal. Therefore, I cannot reliably format my customFunction output via FormattedNumberCellValue for all locales. It must be done via Excel.RequestContext.sync which is not ideal with numerous simultaneous custom functions.

e.g., I am in the US and am returning the following from a custom function:

const fmtNumCellVal: Excel.FormattedNumberCellValue = {
  type: Excel.CellValueType.formattedNumber,
  basicValue: 45708,
  basicType: Excel.RangeValueType.double,
  numberFormat: storedShortDatePattern,
}

...where storedShortDatePattern is retrieved from application.cultureInfo.datetimeFormat.shortDatePattern
(shortDatePattern).

When my Excel-for-Web Regional Settings (File > Options) are set to 'English (United States)' (en-US; "m/d/yyyy") the static serial date is rendered and formatted properly. However, if I change my Excel-for-Web Regional Settings to 'French (France)' (fr-FR; jj/mm/aaaa'), #VALUE! is rendered in the cell.

From the documentation, it seems like this is not possible. numberFormat appears to only support a localized accessor.

Thanks.

[isabela-dominguez]

Thank you for sharing this issue @e16n. Connecting you with @XuanZhouMSFT who may be able to help.

[AlexJerabek]

Hi @e16n,

Have you tried using valuesAsJsonLocal? That should let you set a FormattedNumberCellValue with a localized numberFormat string.

That said, while this will work if you set the FormattedNumberCellValue using valuesAsJsonLocal, it won't work if you try to return the same value as a result of a custom function. Custom functions always use "en-us" locale for parsing its results while valuesAsJsonLocal uses the current user locale. This is a gap in the custom functions platform.

[e16n]

Hi @e16n,

Have you tried using valuesAsJsonLocal? That should let you set a FormattedNumberCellValue with a localized numberFormat string.

That said, while this will work if you set the FormattedNumberCellValue using valuesAsJsonLocal, it won't work if you try to return the same value as a result of a custom function. Custom functions always use "en-us" locale for parsing its results while valuesAsJsonLocal uses the current user locale. This is a gap in the custom functions platform.

Thanks for the response, but the question pertains to a FormattedNumberCellValue, not a Range.

[AlexJerabek]

Thanks for the response, but the question pertains to a FormattedNumberCellValue, not a Range.

Right, but how are you getting your CellValue objects if not through a Range object?

[e16n]

Thank you for sharing this issue @e16n. Connecting you with @XuanZhouMSFT who may be able to help.

@isabela-dominguez @XuanZhouMSFT Can I please get an update?