The TEXT() function is an interoperability and portability nightmare, specifically because MS is/was en-US centric but also chose localized number format code keywords for some locales and separators may differ between locales and there’s no official way to tell the function which locale was used. It’s one of the most crappy things MS-Excel came up with (though probably due to their intention to kill Lotus 1-2-3 and thus implement everything it had but never ever did a cut to stop their own nonsense additions). If you can avoid the TEXT() function then avoid it. But it’s so widely used that resistance is futile…
That being said, implementation is utterly atrocious trying to determine in what locale the format code could actually be and whether it uses localized keywords or not.
If the formula cell has a number format assigned, that format’s locale is taken as a hint for a first guess, else the LibreOffice default locale (which may follow the system locale or be set fixed to a specific locale under Tools->Options). Let’s call this Locale1.
If the format code argument exists already as a number format code for that determined Locale1 then everything is fine and that number format is used [case1].
Else if Locale1 is en-US then the format code argument is scanned in the en-US locale [case2]. If that fails then no other case is tried and an error returned.
Otherwise [all other cases], the format code argument is tried whether it already exists for the en-US locale [condition3].
Also tried is to scan the format code argument in en-US and convert to Locale1 [try4]. From that a valid format entry may result [case5] (or not).
If condition3 is true and try4 was successful, then the resulting format case5 is used.
If condition3 is false and one of try4 failed or the resulting format code string is equal (no syntactical changes) [condition6], then the format code argument is tried to be scanned in Locale1 with locale specific keywords. The result may be a valid format entry [case7], replacing case5 (or not).
Otherwise, if condition3 is false and condition6 is false, then the format code argument is tried to be scanned in Locale1 and converted to en-US. If that is successful and the resulting format code is not equal [condition8], then the format code argument is scanned in Locale1 with localized keywords. From that a valid format entry may result [case9], replacing case5 (or not). If condition8 is false, then there’s still the case5 format entry.
Now if there is a valid number format it is either case5, case7 or case9 (case1 and case2 were already used and ended). No valid number format returns error.
Geez, I’d rather have pointed you to the source code instead of writing this down in prosa from reading the source code
And no, the behaviour is not specified, it has grown empirically trying to suffice the needs of users. There’s also no rule to not implement features that would be too complicated to specify (or rather, even if such thing like this behaviour would had been specified it would had turned out to not be sufficiently specified). You’d also not want to not have TEXT() implemented at all… Btw, some OpenOffice.org feature specifications are still available.
So, to recap: If you have to use the TEXT() function then try to avoid locale dependent separators. Also, try to stick to English format code keywords (i.e. date YMD instead of JMT or AMG or …); which helps in LibreOffice but maybe not in Excel or even does not work there.
Some formulas/functions guess themselves a format for the result, sometimes depending on the format(s?) of referenced cells…
That’s mostly for arithmetic operations (+,-,*,/) i.e. adding currency values or times or adding days to dates and if the result carries a value type a corresponding number format is applied if the cell did not have one applied before.