是否以本机Excel语言动态显示工作日名称? [英] Dynamically Display Weekday Names in Native Excel Language?

问题描述

我正在尝试为国际用户群开发Excel财务模板，该模板以其母语将工作日名称显示为字符串(即，今天="MON").对于英文Excel版本，这很容易:=Text(Now(),"DDD").但是，我很难找到适用于所有Excel语言的通用解决方案.使用我上面的Text()公式，法国和德国用户正在其单元格中获得DDD返回值.我还尝试过=Text(Now(),"*DDD")，该返回一个不一致的整数，即"07".

I'm trying to develop an Excel financial template for an international user base that displays the weekday name as a string (i.e. today = "MON") in their native language. For English Excel versions, this is easy: =Text(Now(),"DDD"). However, I'm having a hard time finding a common solution that works for all languages of Excel. With my above Text() formula, French and German users are getting DDD return values in their cells. I've also tried =Text(Now(),"*DDD") which returns an inconsistent integer i.e. "07".

我知道我可以使用4位十六进制参考数字对显示语言进行硬编码.对于德国，这将是=TEXT(NOW(),"[$-0407]DDD")，它返回令人满意的值"Mo".显然，这对我的整个全球团队都无效，因为我有数百名用户使用十几种语言进行操作，并且还在不断增长.

I know that I can hard-code the display language with a 4 digit hexadecimal reference number. For Germany, this would be =TEXT(NOW(),"[$-0407]DDD") which returns a satisfying value of "Mo.". Obviously, this fails to work for my entire global group because I have hundreds of users operating in over a dozen languages and growing.

是否有一种动态方式返回星期几的母语名称?

我当前的解决方案"是利用选择/工作日功能=CHOOSE(WEEKDAY(NOW(),2),"MON","TUE","..."生成星期的英语版本，但这会引起我的欧洲用户的野蛮暴行，他们希望显示他们的语言工作日名称.

My current "solution" is to leverage the choose/weekday function =CHOOSE(WEEKDAY(NOW(),2),"MON","TUE","..." to generate the English version of the week, but this is creating barbaric outrage from my European users who want their language weekday name to appear.

VBA选项是可接受的.谢谢.

VBA options are acceptable. Thanks.

推荐答案

动态方式返回星期几的母语名称

您可以使用下面的wday函数，例如在法国工作日通过wday("fr")来获取"Lu"(= lundi).该函数使用函数cPattern中的国际模式.

You can use the wday function below, calling e.g. the French weekday via wday("fr") to get "Lu" (= lundi). The function uses the international patterns in function cPattern.

VBA-主要功能

(1)工作日

Function wday(ByVal d As Date, ByVal lang As String) As String
' Purpose: get weekday in "DDD" format
'// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]ddd")
wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
End Function

(2)个月

Function mon(ByVal d As Date, ByVal lang As String) As String
'// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]mmm")
mon = Application.Text(d, cPattern(lang) & "mmm")
End Function

辅助功能

Function cPattern(ByVal ctry As String) As String
' Purpose: return country code pattern for functions mon() and wday() 
' Codes: https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx
ctry = Trim(LCase(Left(ctry & "  ", 3)))
Select Case ctry
  Case "de"
    cPattern = "[$-C07]" ' German
  Case "en"
    cPattern = "[$-809]" ' English UK
  Case "es"
    cPattern = "[$-C0A]" ' Spanish
  Case "fr",  "fre"
    cPattern = "[$-80C]" ' French
  Case "us"
    cPattern = "[$-409]" ' English US
' more ...
End Select
End Function

附录(在评论后编辑)

您可以在cPattern函数中将国际国家代码用作ctry参数的默认值，并将其设置为可选(应变体以使其能够使用IsMissing):

You can use the international Country codes as default value for the ctry argument within the cPattern function and set it optional (should be variant to be able to use IsMissing):

Function cPattern(Optional ByVal ctry As Variant) As String                     ' <<  optional, variant
'
If IsMissing(ctry) Then ctry = Application.International(xlCountrySetting) & "" ' << ADD if no ctry Definition
If Len(ctry) = 0 Then ctry = Application.International(xlCountrySetting) & ""
ctry = Trim(LCase(Left(ctry & "  ", 3)))
Select Case ctry
'
Case "43", "de"         ' << add individual Country Codes
   cPattern = "[$-C07]" ' German
' ...
End Select
End Function

以类似的方式，您应该在wday函数可选和变体中更改第二个参数:

In a similar way you should change the 2nd Argument in the wday function optional and variant:

Function wday(ByVal d As Date, optional ByVal lang) As String
If IsMissing(lang) then lang = ""   ' << if 2nd arg is missing then empty string
wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
End Function

第二次编辑

通常，空模式前缀会自动显示英文文字，但是通过定义其他国家/地区设置(请参见上面的cPattern函数)，可以在辅助功能wday中将其重定向.

Generally an empty pattern prefix would automatically display English writing, but this is redirected in the helper function wday by defining additional country settings (see cPattern function above).

您可以如下更改主要功能以包括DDD格式:

You could change the main functions as follows to include the DDD formatting:

'(1) weekdays
Function wday(ByVal d As Date, Optional ByVal lang) As String
' Purpose: get weekday in "DDD" format
' ----------------------------
' I. If 2nd argument is missing, then use local writing
' ----------------------------
  If IsMissing(lang) Then         ' missing 2nd argument
     wday = Format(d, "ddd")
' ----------------------------
' II. If 2nd argument exists, then search language code prefix to get any defined language
' ----------------------------
  Else                            ' 2nd argument exists
  '// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]ddd")
      wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
  End If
End Function

这篇关于是否以本机Excel语言动态显示工作日名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！