Locale-independent Text function in Excel

Question

I need to format dates in excel, and I'm trying to use the TEXT formula. The problem is that Excel's intepretation of the arguments changes when the locale changes.

For example: if I have a date in cell A1, that i'd like to convert to text, in the year-month-day-format, I have to use =TEXT(A1, "yyyy-mm-dd") if my PC has an English-language locale, but =TEXT(A1, "jjjj-MM-tt") (I kid you not, the M has to be upper case) if it has a German-language locale. This makes the document unportable. (The second argument is plain text and therefore not converted when changing locale.)

Remarks:

• This is just an example, I know I could do the long =YEAR(A1) & "-" & TEXT(MONTH(A1), "00") & "-" & TEXT(DAY(A1), "00") in this case. I'm wondering about the more general case.

• The date should not just be displayed in a certain format, it should actually be a string. For someone viewing the file this doesn't make a difference, but when using it in other formulas, it does.

• I could write a UDF in VBA to solve the issue, but I cannot use VBA in this document.

• I do not care about changing the names of the months etc. It's fine, if the name of the month is June or Juni depending on the locale.

• I want to stress that the issue occurs due to the PC's locale - not due to the GUI language of the MS Office version. In the example above, Excel's GUI and formulas were in English in both examples; I just changed the locale on the machine.

Many thanks

Answer 1

Here is a slightly cheaty method: Use a VLOOKUP on a value that will change based on your System Language - for example TEXT(1,"MMMM")

=VLOOKUP(TEXT(1,"MMMM"),{"January","yyyy-MM-dd";"Januar","jjjj-MM-tt"},2,FALSE)

In English: Text(1,"MMMM") = "January", so we do a VLOOKUP on the Array below to get "yyyy-MM-dd"

"January" , "yyyy-MM-dd" ;
"Januar"  , "jjjj-MM-tt"

Auf Deutsche, Text(1,"MMMM") = "Januar", also wir machen einen SVERWEIS auf dem Array oben, um "jjjj-MM-tt" zu erhalten! :)

Then, just use that in your TEXT function:

=TEXT(A1, VLOOKUP(TEXT(1,"MMMM"),{"January","yyyy-MM-dd";"Januar","jjjj-MM-tt"},2,FALSE))

Obviously, the main reason this works is that TEXT(1,"MMMM") is valid for both German and English. If you are using something like Filipino (where "Month" is "Buwan") then you might find some issues finding a mutually intelligible formatting input.