excel - 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

标签： excel

Answer 1

这是一个有点作弊的方法：VLOOKUP在一个会根据您的系统语言而改变的值上使用 a - 例如TEXT(1,"MMMM")

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

In English: Text(1,"MMMM") = "January"，所以我们VLOOKUP在下面的数组上做一个得到“yyyy-MM-dd”

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

Auf Deutsche, Text(1,"MMMM") = "Januar", 还有 wir machen einen SVERWEISauf dem Array oben, um "jjjj-MM-tt" zu erhalten！:)

然后，只需在您的TEXT函数中使用它：

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

显然，这个工作的主要原因TEXT(1,"MMMM")是对德语和英语都有效。如果您使用的是菲律宾语（其中“月份”是“Buwan”），那么您可能会发现一些问题，即寻找相互理解的格式输入。