XL3LocaliseFormatString

Translates format text from US English to the users current locale, for use with Excel's Text() function. Available from Version 9 onwards.

Rationale

Excel's Text() function takes a format string as an argument. This is different depending on the locale the current user is in, making sharing workbooks with these formulae difficult.

Example

We want to show the year part of a date in $A$1, and our user is French, so we enter the formula

=Text($A$1; "AAAA")

Sadly, when the workbook is shared with a user in Germany we no longer get the expected results.

The solution is to add a second formula, in $A$2

=XL3LocaliseFormatString("yyyy")

and change our original function to

=Text($A$1; $A$2)

Now when the workbook is shared, or published to the web, the format text is translated to the correct language, and we always see the year number.

Reference

The format passed into the XL3LocaliseFormatString is always in US English format.

Placeholder	Usage
"."	Decimal separator
","	Thousand separator
"y"	Year placeholder
"m"	Month or Minute placeholder
"d"	Day placeholder
"h"	Hour placeholder
"s"	Second placeholder