Specifying a Language for the TEXT Function

Hi, on some posts I found out that the letter e replaces the yyyy, jjjj and aaaa etc as a universal code for the (full) year notation: we're trying that out now. It seems to work: would be the best (easiest, most practical) solution for multilingual users, companies, work groups, etc.

I also found another solution which is only using dd and mmm but then leaving the jjjj or yyyy out, and instead using the YEAR function as a separate formula within the longer array. A bit more hassle, but it works, as well. Example (for Spanish, using formulas in English):

="De "&(TEXT(B56;"[$-0C0A]dd mmmm yyyy"))&" hasta "&(TEXT(B365;"[$-0C0A]dd mmmm yyyy"))&" ("&I1&" días de viaje)."
Becomes:
="De "&(TEXT(B56;"[$-040C]dd mmmm"))&" "&YEAR(B56)&" hasta "&(TEXT(B365;"[$-040C]dd mmmm"))&" "&YEAR(B365)&" ("&I1&" días de viaje)."

But the best solution still seems:
="De "&(TEXT(B56;"[$-0C0A]dd mmmm e"))&" hasta "&(TEXT(B365;"[$-0C0A]dd mmmm e"))&" ("&I1&" días de viaje)."

I hope this answer finds you and next readers well :-)
I am not sure if it works for languages that don't use/read dd as day and mmm as month: in our case working mostly with English, Spanish and Dutch it works because it's the same letters...

Cheers,
Guido

Hi Allen,

Interesting post and I've used it, too. However a problem has popped up and I can't find anything about it. I hope you can help.

I have colleagues in different parts of the world, working in different languages. We all use Excel on a daily basis, in five languages.

I am the one always coming with improvements to our standard (shared) Excel templates. In the latest version, I used the TEXT function to fill in a travel period automatically: "from 1 October 2019 to 20 October 2019". With the language codes it seemed to be working fine in every language. However, we now found out that the language of Excel (Office) itself, so the locale settings of each colleague, is causing trouble.

On my Dutch computer, I made the formula like this: ="De "&(TEKST(B56;"[$-0C0A]dd mmmm jjjj"))&" hasta "&(TEKST(B365;"[$-0C0A]dd mmmm jjjj"))&" ("&I1&" días de viaje)." (This is an example in Spanish)
However, my colleagues aren't seeing the years correctly: they see "jjjj".
When they change it to "yyyy" it's fine for them (so I thought, let change it all to the unversal language English, jeee!), but then I see yyyy instead of the year, arghhh....

Is there some sort of universal character to use?? Thanks a lot, I'm sure there are more people suffering this...

Best regards,

Guido