日期格式在不同系统中表现出色德语到英语 [英] Date format excel in diferent systems German to English

问题描述

我遇到了一个问题，我尝试在excel als中使用IF函数导出日期，

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");")

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

它是德语，英语是

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

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

我的计算机"的语言为德语，因此效果很好，但是我在美国的同事却遇到了问题，他们无法导出带有正确日期的日期，今天以"JJJJ16TT"为例显示，可以.t显示年份和日期...

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.

感谢和问候.

推荐答案

两种可能的方法:

• 不要使用TEXT函数.
  • 将公式更改为:

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

   - and apply a number format to the cell without the LCID.
  

  • 特别是如果您的代码是较长函数的一部分，则可以
  • 为日期字符串使用定义的名称
  • 创建一个Workbooks_Open事件以根据用户的语言更改该名称

  将公式更改为: = WENN(C2 ="Y"; TEXT(HEUTE(); dtFormat);")

  例如:

  要输入此宏(子)，请使用< alt-F11> 打开Visual Basic编辑器.在 Project Explorer 窗口中，选择相关VBA项目下的 ThisWorkbook .

  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
  

  这篇关于日期格式在不同系统中表现出色德语到英语的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！