☰

如何防止 Excel 在公式中使用操作系统区域设置作为日期模式

tag-icon • tag-icon microsoft-excel-2013 date-time text-formatting localization
如何防止 Excel 在公式中使用操作系统区域设置作为日期模式

根据这个问题我有以下问题：

我想使用一些 Excel 函数（不是单元格格式），例如 TEXT(A1, {date_pattern})

但是回答我之前问题的人让我发现日期模式根据 Windows 区域设置而改变。

但是，我的操作系统 (Windows 7) 是英文的，Office 套件也是英文的。通过查看我的区域设置，它甚至显示使用英文符号 (dd.MM.yyyy) 的模式

地区和语言

我想知道是否有任何方法可以从 Excel 中禁用这种行为，这意味着我希望始终使用英文模式，而从不使用本地化模式，因为我不希望我的 Excel 表的行为根据读者的本地化而改变。

一个简单的例子是将某个日期字段重新格式化为以计算机为中心的方式，例如：“yyyymmdd_hhss”，这是普遍认可的，并且可以轻松地上下排序。但由于我在瑞士法语区，我应该写“aaaammjj_hhss”，如果我将这个 Excel 发送给苏黎世的同事，他将无法看到正确的日期，因为他使用的是瑞士德语本地化版本（他的 Excel 应该为“jjjjmmtt_hhss”）

我们很聪明，用英文安装了所有的 Windows 和 Office，但我们仍然面临这样的问题，因为这链接到操作系统区域设置。

对我来说，更改 Windows 设置不是一个选项，因为所有其他程序都在使用此设置。

答案1

这个问题有点老了，但到目前为止似乎还没有答案。我也在许多网站上看到过类似的问题，但还没有找到任何仅涉及内置 Excel 函数的答案。但是，使用 VBA 解决这个问题相当容易。您甚至不必知道如何编程来做到这一点，因为所需的函数非常简单。

打开工作簿后，只需按Alt+F11（打开 VBA 编辑器）然后单击菜单项插入>模块 在新的 VBA 模块中，输入以下内容：

Public Function FMT$(ByVal Value, ByVal strFormat)
    FMT = VBA.Format$(Value, strFormat)
End Function

这样做只是使用 VBA 自己的Format函数而不是 Excel 的TEXT函数。这正是您想要的。

此新函数将根据您指定的格式字符串格式化任何日期（或数字）。它将使用标准 (英文) 符号，而不管操作系统的区域设置如何。

要在工作簿中使用它，只需键入=FMT(A1, "yyyymmdd_hhss")而不是=TEXT(A1, "yyyymmdd_hhss")。（当然，您可以根据需要更改单元格引用和格式字符串。）

顺便说一句，您可以随意命名该函数。我之所以选择这个函数，FMT是因为它很短，而且该函数可以格式化数字和日期。但是，如果您愿意，也可以选择更具描述性的名称。只需记住在工作表中使用与 VBA 代码中相同的名称即可。

请注意，VBA 格式字符串与 Excel 的自定义格式字符串并不完全相同（例如，使用“n”而不是“m”表示分钟），但它们非常相似。查看这里有关详细信息，或在 MSDN 中搜索“Format Function (Visual Basic for Applications)”。滚动到底部可查看各种日期格式说明符。

方法 2

另一种方法也使用 VBA，但实际上可能更容易维护，如下所示：

  1. 使用常规的“单元格格式”对话框将所需的日期格式应用于单元格。如果您愿意，此单元格可以位于隐藏的工作表上 - 它不需要显示给最终用户。假设您应用了格式yyyymmdd\_hhss单元格1澳元（请注意，下划线必须按照所示进行转义）。
  2. 将GetFormat函数（如下所示）添加到工作簿中的 VBA 模块。
  3. 进入=GetFormat($A$1, TRUE)另一个单元格（例如$B$1）
  4. 该函数将返回本地化格式字符串的版本。因此，即使你最初格式化1澳元使用yyyymmdd\_hhss，当您使用法语（例如）在计算机上打开工作簿时，该函数将显示aaaammjj\_hhss。
  5. 现在，只需在所有TEXT函数中引用第二个单元格即可。例如：=TEXT(F22, $B$1)。

以下是 VBA 代码：

Public Function GetFormat$(Cell As Range, Optional ByVal UseLocal As Boolean)
    If UseLocal Then
        GetFormat = Cell.NumberFormatLocal
    Else
        GetFormat = Cell.NumberFormat
    End If
End Function

这样，我们就可以“检查”您最初格式化的单元格 ($A$1) 以检索本地化的格式字符串。该字符串将表示您应用的相同格式，但它将使用 TEXT 解释的正确字母（例如“j”而不是“d”），因此显示的日期值在所有语言环境中都是恒定的。如果您希望整个工作簿只使用一种日期格式，则只需执行步骤 1-4 一次。然后在您当前使用它的所有单元格中重复步骤 5（TEXT 函数），但不是硬编码格式，而是引用包含本地化格式字符串的单元格 ($B$1在示例说明中）。

请注意，第二个参数GetFormat告诉函数是否返回本地化版本（取决于区域设置）或“标准”版本（始终基于 en-US）。

这里有一些截图可能会使这一点更加清楚。

英文

  • 图中，第 1 列列出了应用了不同格式的单个日期的几种表示形式。
  • 请注意，第 2 行和第 3 行使用 Excel 的“系统默认”日期格式。（这些格式在“格式对话框”中以星号开头，表示应使用用户的默认日期格式。）还请注意，第 5 行使用带括号的 LCID，强制将月份和日期名称使用的语言设置为英语（可以使用不同的 LCID 来指定其他语言）。
  • GetFormat(Cell, FALSE)第二列显示第 1 列中每个单元格的结果。（回想一下，FALSE第二个参数导致函数返回非局部格式）。
  • 第三列显示GetFormat(Cell, TRUE)第 2 列中每个单元格的返回值。（即本地化格式）。
  • 第四列显示了使用原始日期值和本地化的结果GetFormat将重新生成第 1 列中显示的格式。但请注意，此列未应用任何数字格式。这些值是 TEXT 函数的直接结果。

上述英语（美国）案例的结果并不十分有趣，但您可以将它们与通过将我的操作系统的区域设置更改为其他各种区域设置而获得的结果进行比较。重要的是，请注意，通过GetFormat结合使用TEXT，我们能够在所有区域设置中保留数字格式（不包括日期或月份名称的格式）的恒定结果。通过使用 LCID 限制语言（如第 5 行所示），我们甚至可以在包含日期和月份名称时保留恒定格式。

甲基安替比林

一氧化氮

ru-ru

此方法适用于大多数地区，但应注意，用于表示印度-阿拉伯数字的脚本并非在所有地区都相同。因此，尼泊尔（印度）地区等区域设置将导致日期格式“看起来”与 en-US 日期不同。但就数字的位置而言，它们实际上采用相同的“格式”——它们只是使用不同的数字符号。

内因

答案2

尽管这个问题有点老了，但我还是遇到了另一个（简单！）解决方案，为了完整性/参考性，我在这里发布。

  • 我成功尝试了 ricovox 的答案，但想要一个不使用 VBA 的解决方案。

  • 也测试了 MarinD 的解决方案。但是，正如评论中指出的那样，如果您需要满足多个国家/地区特定的格式，那么它很快就会变得难以处理。

  • 现在来看看我的解决方案：使用 Excel 的原生 YEAR()、MONTH() 和 DATE() 函数，将其包装在 TEXT() 中以在前面填充零。使用 A1 中的日期：

    =TEXT(YEAR(A1),"0000")&"-"&TEXT(MONTH(A1),"00")&"-"&TEXT(DAY(A1),"00")

    给我格式的日期YYYY-MM-DD，无论当前区域设置是否要求YYYY-MM-DD或AAAA-MM-JJ或年份/月份/日期的任何其他本地化名称。

    Excel 会自动翻译其原生公式。

答案3

对于日期，您实际上可以定义一种格式，使 Excel 使用特定的区域设置。如果您将包含日期的单元格的数字格式设置为类似

[$-409]m/d/yy h:mm AM/PM;@

Excel 将以美国语言环境（十六进制 409）显示您的日期

问题：YMD 符号已本地化，因此在德语系统上会转换为 JMT。因此，您可能需要根据自己的情况调整格式字符串，但我希望它在使用不同语言的系统上表现得像公式（自动翻译）（您似乎已经意识到了这一点）。

请参阅此处了解更多详细信息： https://stackoverflow.com/questions/894805/excel-number-format-what-is-409

以下是区域设置列表： http://support.microsoft.com/kb/221435

答案4

如果您的参考日期在单元格 A1 中：

=IF(RIGHT(TEXT(A1;"dd-mm-yyyy");1)="y";TEXT(A1;"jj-mm-aaaa");TEXT(A1;"dd-mm-yyyy"))

相关内容