Excel TEXT(dd.mm.yyyy) function and different client languages

Question

How can I make a function with a formatted date that will work in every language? I want to make the following function:

=CONCATENATE(TEXT(C8;"TT.MM.JJJJ");"/";G8)

The problem here is, that I use the english client but because I'm a german, excel forces me to use T for day and J for year. I think this will cause problem on a PC located in england (for example).

I think [$-409] won't work because I still have to use T for day and J for year. Is there a good solution for this (function wise)?

Answer 1

If you pass the value of a formula in "" then it cannot be changed based on the localisation settings.

A good way to do it is to use a custom function with VBA, returning "TT.MM.JJJJ" if you are in Germany and "DD.MM.YYYY" if you are in England.

Public Function CorrectString() As String

    Select Case Application.International(XlApplicationInternational.xlCountryCode)
        Case 1
            CorrectString = "DD.MM.YYYY"
        Case 49
            CorrectString = "TT.MM.JJJJ"
        Case Else
            CorrectString = "ERROR"
    End Select

End Function

Would allow you to call the function like this:

=CONCATENATE(TEXT(C8;CorrectString());"/";G8)

And depending on the excel language, it would give either the German or the English versions.

To simplify the formula, try calling only:

 =TEXT(21322;CorrectString())

This should return 17.05.1958.

Source for the regional languages, mentioned by @Dan at the comments: https://bettersolutions.com/vba/macros/region-language.htm

Or run this to see the corresponding number of your current Excel:

MsgBox xlApplicationInternational.xlCountryCode