excel - Automatically adjust Date argumentsformat in another localelanguage - Stack Overflow

admin管理员组

文章数量:1415673

I have English Office. Building a macro that builds a string using dates:

Range("G2").Formula = "=TEXT(TRIM(F2),""dd.MM.yyyy"")"
Range("G2:G" & LastRowNum).FillDown

or say

Range("R2").Formula = "=H2&""-""&TEXT(today(),""ddMMyyyy"")&TEXT(now(),""HHmm"")"
Range("R2:R" & LastRowNum).FillDown

I get the correct string.

Colleagues have Office in a different language.
For them, this is unable to build a string. I have to convert my VBA code to reflect their locale.
How do I automatically adjust the formula\VBA to the user's locale, whether it be Portuguese or Russian?

I tried * (asterisk).

Range("R2").Formula = "=H2&""-""&TEXT(today(),""*ddMMyyyy"")&TEXT(now(),""*HHmm"")"
Range("R2:R" & LastRowNum).FillDow

I tried mentioning that formula was built in English, thinking that Excel would "translate" it to user's locale.

Range("R2").Formula = "=H2&""-""&TEXT(today(),""[$-0409]ddMMyyyy"")&TEXT(now(),""[$-0409]HHmm"")"
Range("R2:R" & LastRowNum).FillDow

I have English Office. Building a macro that builds a string using dates:

Range("G2").Formula = "=TEXT(TRIM(F2),""dd.MM.yyyy"")"
Range("G2:G" & LastRowNum).FillDown

or say

Range("R2").Formula = "=H2&""-""&TEXT(today(),""ddMMyyyy"")&TEXT(now(),""HHmm"")"
Range("R2:R" & LastRowNum).FillDown

I get the correct string.

Colleagues have Office in a different language.
For them, this is unable to build a string. I have to convert my VBA code to reflect their locale.
How do I automatically adjust the formula\VBA to the user's locale, whether it be Portuguese or Russian?

I tried * (asterisk).

Range("R2").Formula = "=H2&""-""&TEXT(today(),""*ddMMyyyy"")&TEXT(now(),""*HHmm"")"
Range("R2:R" & LastRowNum).FillDow

I tried mentioning that formula was built in English, thinking that Excel would "translate" it to user's locale.

Range("R2").Formula = "=H2&""-""&TEXT(today(),""[$-0409]ddMMyyyy"")&TEXT(now(),""[$-0409]HHmm"")"
Range("R2:R" & LastRowNum).FillDow

• excel
• vba
• date
• locale

Share Improve this question Follow edited Mar 18 at 11:15 CommunityBot 111 silver badge asked Feb 9 at 10:05 user2858200user2858200 13311 gold badge22 silver badges99 bronze badges 7

• Try to apply Format function from VBA. It returns results in the local language. – MGonet Commented Feb 9 at 10:32
• IF you can modify the workbook being used (eg Add a Workbook_Open macro and and Name'd value), then this solution will work for you. Otherwise, you may need a list of the possible languages. – Ron Rosenfeld Commented Feb 9 at 19:13
• Do you need the range to actually contain a formula? Or do you just want the range to have the result? – Evil Blue Monkey Commented Feb 10 at 21:27
• Anyway, you might consider using the DAY, MONTH and YEAR functions (along with TEXT) to create a string of your liking. You might also use the DATE function to reconstruct the date from your original data and then work with that if you need to use it in a calculation. – Evil Blue Monkey Commented Feb 10 at 22:44
• @RonRosenfeld i actually didn't understand till the end proposed solution and it looked a bit clumsy to me. need to dedicate more time later. – user2858200 Commented Mar 11 at 20:24

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

International Date and Time Formatting

• In your first case, it makes sense to have the date format in the formula since when you change a value in the F column, the value in the G column gets updated, although I have no idea how TRIM fits in. Let us know!?
• In your 2nd case, when you use the volatile TODAY() and NOW() functions, it doesn't since the value of time changes on each calculation. But you can still use the functions (out-commented code). It makes sense to use VBA's Format function instead, to return the current time and keep it.
• For such simple formulas, you don't need to use the AutoFill method.
• The code you were trying to utilize ([$-0409] or the equivalent [$-en-US]) has nothing to do with your case. It is used to display months and/or days 'fully' or abbreviated (3 chars) in English no matter the locale. If you want to display the full month name in German on your English system, you could use something like =TEXT(A1,"[$-407]mmmm") or the equivalent =TEXT(A1,"[$-de-DE]mmmm"), assuming cell A1 holds a date.

Examples

Sub Test()
    
    Dim LastRowNum As Long: LastRowNum = 5 ' just to compile!
    
    Range("G2:G" & LastRowNum).Formula = "=TEXT(TRIM(F2),""" _
        & IntDateFormat("dd.MM.yyyy") & """)"
    
    'Range("R2:R" & LastRowNum).Formula = "=H2&""-""&TEXT(TODAY(),""" _
        & IntDateFormat("ddMMyyyy") & """)&TEXT(NOW(),""" _
        & IntTimeFormat("HHmm") & """)"
    
    Range("R2:R" & LastRowNum).Formula = "=H2&""-""&" _
        & Format(Now(), "ddmmyyyyhhmm")

End Sub

The Functions

• There are probably aspects I didn't consider but the basics are covered. You could certainly dumb them down since you currently don't need the additional functionalities, but they could come in handy when you start tackling conditional formatting.

Function IntDateFormat( _
    ByVal DateFormat As String, _
    Optional ByVal CorrectDateSeparator As Boolean = False, _
    Optional ByVal CorrectDateOrder As Boolean = False) _
As String

    Dim SplitDate() As String:
    SplitDate = Split(StrConv(DateFormat, vbUnicode), Chr$(0))
    ReDim Preserve SplitDate(UBound(SplitDate) - 1)
    
    Dim i As Long
    
    With Application
        For i = 0 To UBound(SplitDate)
            Select Case SplitDate(i)
                Case "Y", "y": SplitDate(i) = .International(xlYearCode)
                Case "M", "m": SplitDate(i) = .International(xlMonthCode)
                Case "D", "d": SplitDate(i) = .International(xlDayCode)
                Case "/"
                    If CorrectDateSeparator Then
                        SplitDate(i) = .International(xlDateSeparator)
                    End If
            End Select
        Next i
        If CorrectDateOrder Then
            Select Case .International(xlDateOrder)
                'Case 0 ' MDY
                Case 1 ' DMY
                    For i = 0 To UBound(SplitDate)
                        Select Case SplitDate(i)
                            Case "M": SplitDate(i) = "D"
                            Case "m": SplitDate(i) = "d"
                            Case "D": SplitDate(i) = "M"
                            Case "d": SplitDate(i) = "m"
                        End Select
                    Next i
                Case 2 ' YMD
                    For i = 0 To UBound(SplitDate)
                        Select Case SplitDate(i)
                            Case "M": SplitDate(i) = "Y"
                            Case "m": SplitDate(i) = "y"
                            Case "Y": SplitDate(i) = "M"
                            Case "y": SplitDate(i) = "m"
                        End Select
                    Next i
            End Select
        End If
    End With
    
    IntDateFormat = Join(SplitDate, "")

End Function

Function IntTimeFormat( _
    ByVal TimeFormat As String, _
    Optional ByVal CorrectTimeSeparator As Boolean = False) _
As String

    Dim SplitTime() As String:
    SplitTime = Split(StrConv(TimeFormat, vbUnicode), Chr$(0))
    ReDim Preserve SplitTime(UBound(SplitTime) - 1)
    
    Dim i As Long
    
    With Application
        For i = 0 To UBound(SplitTime)
            Select Case SplitTime(i)
                Case "H", "h"
                    SplitTime(i) = .International(xlHourCode)
                Case "M", "m", "N", "n"
                    SplitTime(i) = .International(xlMinuteCode)
                Case "S", "s"
                    SplitTime(i) = .International(xlSecondCode)
                Case ":"
                    If CorrectTimeSeparator Then
                        SplitTime(i) = .International(xlTimeSeparator)
                    End If
            End Select
        Next i
    End With
    
    IntTimeFormat = Join(SplitTime, "")

End Function

本文标签： excelAutomatically adjust Date argumentsformat in another localelanguageStack Overflow