CODEWITHSUNDEEP
excel
Super Eiskalt
Super Eiskalt

Reputation: 25

Date format excel in diferent systems German to English

I came across a problem, I try to export a date by using IF-function in excel als followed,

=WENN(C2="Y";TEXT(HEUTE();"JJJJMMTT");"")

it is in German, which in English is,

=IF(C2='Y'; TEXT(TODAY();"YYYYMMDD");'')

The language of My computer is in German, so it works well, but my colleagues in USA had problem, which they can't export the date with correct date, it shows today as example "JJJJ16TT", it can't show the year and day...

I don't know how to set it right. So I try to ask help from you.

Thanks and best regards.

Upvotes: 2

Views: 3318

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Two possible methods:

  • Don't use the TEXT function.
    • Change your formula to:
     =WENN(C2="Y";HEUTE();"")

 - and apply a number format to the cell without the LCID.
  • Especially if your code is part of a longer function, you can
  • Use a Defined Name for the date string
  • Create a Workbooks_Open event to change that Name depending on the language of the user

Change your formula to: =WENN(C2="Y";TEXT(HEUTE();dtFormat);"")

eg:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor. In the Project Explorer window, select ThisWorkbook under the relevant VBA Project.

Paste the code below into the window that opens.

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), 2)
    dyCode = WorksheetFunction.Rept(.International(xlDayCode), 2)
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 = yrCode & mnthCode & dyCode
ThisWorkbook.Names.Add _
 Name:="dtFormat", _
 RefersTo:="=""" & dtCode & """", _
 Visible:=False

End Sub

Upvotes: 2

Related Questions