Dynamically Display Weekday Names in Native Excel Language?

Question

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".

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.

Is there a dynamic way to return the native-language day of the week name?

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 options are acceptable. Thanks.

Possible duplicate of [VBA - Change date language](https://stackoverflow.com/questions/32090315/vba-change-date-language) — braX, Jan 08 '18 at 17:48
@braX that's not the same question. I am not trying to consistently convert to any single language. I could do that as I explained in my example using the German numeric code. I'm trying to dynamically display the name for a variety of users. So Germany wants MON to = "Mo." Spanish wants to see= "Iu" etc. — pgSystemTester, Jan 08 '18 at 17:54
@Moreno thanks I'll try. Takes me a couple minutes to get an international users machine. What language does it work for you? — pgSystemTester, Jan 08 '18 at 17:59
Friendly hint: `DDDD` only displays the full weekday's name of a given date (e.g. "lundi" in French for monday), "DDD" is the abbreviated Format (e.g. "lun." instead of "lundi"). — T.M., Jan 08 '18 at 19:38
@T.M. yes, I did know that I was using the abbreviated syntax of DDD and DDDD and what they were displaying. I don't care if I get the abbreviated name or not, just so long as there's no uprising from my users. Apparently, all of Europe left work at 3PM, so I still haven't been able to test anything from their perspective... — pgSystemTester, Jan 08 '18 at 20:21
Sorry, no positive contribution.....but "dddd" (or "DDDD") in text function won't work any better than "ddd" - if you can change regional settings in control panel you can change to German to test - you'll just get "dddd" because German needs "aaaa" for day name — barry houdini, Jan 08 '18 at 21:07
I have seen different languages give different results of “DDDD”. I did figure out there needs to be a space between the * and the DDDD. Regardless, I think the vba answer is the only sure answer. Thanks for your help. — pgSystemTester, Jan 09 '18 at 22:25

T.M. · Accepted Answer · 2018-01-09T09:10:48.377

Dynamic way to return the native-language day of the week name

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 - main functions

(1) weekdays

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) months

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

Helper 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

Addendum (Edit after comment)

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

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

2nd Edit

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).

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

Thanks. I gave you a helpful tick, but that's not quite a dynamic solution in that I believe I would have to have the users' declare their language (?). Additionally, as new languages are added, I'd have to update my case VBA, or else write for quite a few to start. Also, your code has a small error. No comma after 'Case "de", Still trying to test the option of "TEXT(NOW(),"DDDD"). — pgSystemTester, Jan 08 '18 at 18:37
@PGCodeRider, I added an approach to get the country code as default value. — T.M., Jan 08 '18 at 19:10
Cool, I'm going to accept your answer but I want to see if we can use the DDDD function in Text. If so, just include in your answer. If not, I'll accept it anyway. Also, I think you're making it a little overly complex with the country variant. I really like the idea of using application.international(Xlcountrysetting), but just set that to an integer. No need to optionally pull information and try to pick between the two. Not a big deal, like I said, I'll give you credit for the answer either way tomorrow. — pgSystemTester, Jan 08 '18 at 20:35

Dynamically Display Weekday Names in Native Excel Language?

1 Answers1

Linked