Multiple language date formats in Excel with VBA

Question

I'm trying to have the current date recognized in Excel using an =TEXT(TODAY(),"mmmm d yyyy") function, but I have run into issues with Canadian users based in Quebec, who may have a system install set to English or French, and likewise an Office install in one language or the other. For today's date, my spreadsheet pulls March 10 2021 on my computer, but the result has been askew for each Quebec-based user I have tested with.

In order to resolve this, I set up a VBA function to pull the application's language setting, with the intention to use a simple IF to check the language code and change from "mmmm d yyyy" to "mmmm j aaaa" (the letters to match the French journée for day and année for year, as per consulting the formats available on French Excel's TEXTE function). My function worked... but the output has come back incorrect each time.

Public Function LangCheck()
  Dim lang_code As Long
  lang_code = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
  LangCheck = lang_code
End Function

So that did its job, but the outputs should have been March 10 2021 or mars 10 2021. Instead from two different users I have received:

March j Wednesday, from a user with a French Excel setting, confirmed by the code outputting language ID 1036.

mars d yyyy, from a user with an English Excel setting on a French OS, confirmed by the code outputting language ID 1033.

Excel is translating every function to its French cognate, but won't translate the formats for the TEXT function on its own. Does anyone have a suggestion for a VBA or function-based solve to this conundrum so that I can ensure consistent dates return regardless of language?

Write 3 versions of the text() function and use if() to choose between them, or use the choose() function to do the same. — Solar Mike
– Solar Mike, Commented Mar 10, 2021 at 21:45
Tried that in the test file. The IF picked up that the language code was wrong and went to the appropriate fork, but then the adjusted date format for French didn't work (somehow delivered Wednesday instead). — afroakuma
– afroakuma, Commented Mar 10, 2021 at 21:49
Well, not going to trawl through my backups but did something similar as we had to send a template with macro (excel 4 macro language anyone??) to several EU countries 'cos we worked in all the markets (and we had combinations of markets) and we found the system language setting (needed it to set the month axis info on the charts) then used choose() to sort the formatting. so you must be missing something. — Solar Mike
– Solar Mike, Commented Mar 10, 2021 at 21:53

Ron Rosenfeld · Accepted Answer · 2021-03-11 02:01:11Z

0

You can use the Application.International properties to extract the correct DMY tokens, and use a named reference for the text function format code:

Change your formula to:

=TEXT(TODAY(),"dtFormat")

And store the below code into the ThisWorkbook module

Wherever the file is opened, the Sub will run and ensure that the dtFormat name has the appropriate DMY codes for the locale.

Option Explicit
'change text function date code
Private Sub Workbook_Open()
    Dim yrCode As String, mnthCode As String, dyCode As String
    Dim dtCode As String
    Dim nM As Name
    
With Application
    yrCode = WorksheetFunction.Rept(.International(xlYearCode), 4)
    mnthCode = WorksheetFunction.Rept(.International(xlMonthCode), 4)
    dyCode = WorksheetFunction.Rept(.International(xlDayCode), 1)
End With

'Can only add a name if it is absent
For Each nM In ThisWorkbook.Names
    If nM.Name = "dtFormat" Then
        nM.Delete
        Exit For
    End If
Next nM

dtCode = mnthCode & " " & dyCode & " " & yrCode
ThisWorkbook.Names.Add _
 Name:="dtFormat", _
 RefersTo:="=""" & dtCode & """", _
 Visible:=False

End Sub

answered Mar 11, 2021 at 2:01

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

afroakuma Over a year ago

Ooh... that could be the ticket. Will test it when I have access to a Quebec user tomorrow. Much obliged!

Ron Rosenfeld Over a year ago

@afroakuma Let me know. If he "opens" the file, the code should run and create the dtFormat named reference.

afroakuma Over a year ago

Hm. For some reason it isn't running. Output is 11tFor3at, which means it ignored the named format entirely and just picked up the d and the m.

afroakuma Over a year ago

ThisWorkbook, as instructed.

Ron Rosenfeld Over a year ago

@afroakuma I assume you also changed the worksheet formula and replaced "mmmm d yyyy" with dtFormat. So please put a Stop after the Next nM line. Save and re-open the file. Then, when it stops, type ?dtCode in the immediate window and let me know what prints out. Then change your Windows Regional Settings to French(Canada) and do the same thing. But also see what happens to the formula output on your worksheet.

|

Collectives™ on Stack Overflow

Multiple language date formats in Excel with VBA

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related