如何更改 Excel 中的日期格式：您需要知道的一切

您从网站下载了一份报告，用 Excel 打开后，每个日期都变成了一个五位数。更糟的是，同一列里混杂着美式和欧式日期格式。

有的写着 03/07/2025，您在想：这是 3 月 7 日，还是 7 月 3 日？

如果这让您开始怀疑自己的数据，也没关系——我们都经历过。

Excel 很好用，但其日期的默认处理并不总是直观。在本教程中，我们将带您了解如何更改 Excel 中的日期格式，以及在导入杂乱数据时如何避免那些常见且恼人的错误。

了解 Excel 的日期管理

在后台，Excel 有自己存储和解释日期的方式。如果您不了解其原理，在共享文件或跨系统协作时可能会出错。

下面就来看看它是如何工作的。

序列号架构

Excel 将日期存储为序列号。例如，1900 年 1 月 1 日存储为数字 1，1900 年 1 月 2 日为 2，以此类推。这便于 Excel 计算诸如两个日期之间的天数等内容。

不过，日期系统有两种：

1. 1900 系统：从 1900 年 1 月 1 日开始计数（Windows 上的默认设置）。
2. 1904 系统：从 1904 年 1 月 1 日开始，主要用于 Mac。

如果您在使用另一种系统的设备上打开由某一系统创建的文件，日期可能会相差约四年。

例如，在 Mac 上输入的 01/01/2025，在 Windows 电脑上打开时，可能显示为 01/01/2021，因为两种系统的起始点不同。

这会造成严重混淆。因此，若要跨平台共享工作簿，请检查文件使用的是哪个日期系统。

区域设置集成

Excel 在显示日期时会遵循您电脑的区域设置。

例如，在美国通常按月/日/年书写日期，因此 03/07/2025 表示 2025 年 3 月 7 日。而在英国或澳大利亚等很多国家，日期格式为日/月/年，于是 03/07/2025 会被解读为 2025 年 7 月 3 日。

假设您把一个 Excel 文件发给其他国家的人。如果对方电脑设置了不同的区域格式，您文件中的同一日期在对方那里可能会显示不同，即使日期未变动，公式也可能报错。

因此，建议在跨地区共享文件时，务必复核日期格式，或使用清晰的格式，如完整月份名称（例如 July 3, 2025）。

Excel 如何解释两位数年份

当我们在日期中输入类似 25 这样的年份时，Excel 必须判断我们指的是 1925 还是 2025。

默认情况下，它会认为：

• 年份在 00 到 29 之间，表示 2000 到 2029。

• 年份在 30 到 99 之间，表示 1930 到 1999。

因此，如果我们输入 4/5/25，Excel 会将其视为 2025 年 4 月 5 日，而不是 1925 年。但如果输入 4/5/30，Excel 会显示 1930 年 4 月 5 日。

这一规则来自 Windows 设置，而非 Excel 本身。您可以更改，但它会影响电脑上所有程序，而不仅仅是 Excel。

因此，建议使用四位数年份而不是修改设置，这样更清晰且便于管理。

应用预定义日期格式

如果您不喜欢 Excel 的默认日期格式，可以根据数据展示需求从大量样式中进行选择。下面看看其中一些选项：

使用“设置单元格格式”对话框

“设置单元格格式”对话框用起来很简单。步骤如下：

1. 右键单击包含日期的单元格或区域，选择设置单元格格式。

2. 按 Ctrl+1（一个Excel 快捷键）。此时会出现设置单元格格式对话框。

3. 在数字选项卡下，从列表中单击日期。

4. 从可用选项中选择所需的日期格式并单击确定应用。

您会看到各种预定义日期格式。但在应用格式之前，先快速看一下“示例”框（如下图所示）。它会展示所选格式下日期的实际显示效果。

使用“设置单元格格式”对话框更改日期格式。作者供图。

“开始”选项卡的快速格式

您也可以通过“开始”选项卡快速设置格式。只需：

1. 选择单元格，转到数字组。
2. 单击下拉菜单。
3. 选择短日期或长日期（下面示例使用了长日期）。

这种方式更快捷，但限制更多。只有两个基础选项，无法控制月份或日期的显示方式等细节。如果您需要更多可选项，建议使用“设置单元格格式”对话框。

通过“开始”选项卡更改日期格式。作者供图。

创建自定义日期格式

如果不想使用 Excel 的预定义日期格式，您可以按需自定义。

语法与代码组成

自定义日期格式使用特殊代码来指示 Excel 如何显示日期的各个部分：

例如：

您可以按任意顺序组合这些代码来创建所需的精确格式。也可以随意使用连字符、斜杠、逗号等分隔符。例如：

设置自定义格式语法的方法：

1. 选中单元格或区域，使用快捷键 Ctrl+1 打开设置单元格格式对话框。

2. 在数字选项卡下，选择自定义。

3. 然后在类型框中输入格式，如下图所示。

自定义日期格式。作者供图。

就这样，您的自定义日期格式语法已设置完成。

针对不同语言环境的自定义格式

您还可以为不同的区域习惯创建自定义格式。

更进一步，您可以强制 Excel 使用特定区域设置，使格式适应某个国家的语言或日期样式。

方法是在格式开头用方括号添加区域代码，例如：

• [$-409]dddd, mmmm d, yyyy —— 使用美式英语。

• [$-804] 表示中文。

• [$-407] 表示德语，等等。

根据不同语言环境自定义日期格式。作者供图。

高级自定义场景

有时您需要超越标准日期显示。例如，想根据日期显示财务季度，如 Q1 或 Q3；或者需要显示一年中的周序号。

虽然 Excel 不能直接通过自定义日期代码支持这些，但您可以结合公式与自定义格式来实现。

方法如下：

• 显示季度：使用公式 ="Q"&INT((MONTH(A1)-1)/3)+1。这会显示该年的季度编号。如果想显示为 Quarter 1，将公式改为 ="Quarter "&INT((MONTH(A2)-1)/3)+1

• 显示周序号：使用 =WEEKNUM(A1)。这会显示一年中的第几周。

此外，您可以在格式中用双引号直接添加文本字面量。例如：

=TEXT(A2, "dddd, mmmm d") & " — Year: " & TEXT(A2,"yyyy")

这会以可读句子的形式显示日期，如：Saturday, July 5 — Year: 2025

或者，您甚至可以添加诸如“Due on:”这样的文本：

=TEXT(A2, """Due on"" dddd, mmmm d")

这样，Excel 会将“Due on”视为纯文本，随后接上单元格 A2 中的完整星期与日期。

用季度代替标准日期显示。作者供图

注意：使用格式代码要小心。m 可能表示月份或分钟，取决于它出现的位置。

• 如果位于小时代码（如 h）之后（h: mm），在 Excel 中会被视为分钟。

• 如果位于日期之后（如 mm/dd/yyy），则表示月份。

管理与转换非日期条目

在 Excel 中，看起来像日期的内容并不一定真的是日期。有时，日期以纯文本录入，这会在您尝试设置格式或进行计算时引发问题。

将文本转换为日期

当您从其他系统（如 CSV 文件或数据库）复制数据时，带日期的单元格可能看起来像日期，但 Excel 实际上将其视为纯文本，而非真正的日期。

要修复，可以用两种方法：

1) DATEVALUE 函数

该函数可将以文本存储的日期转换为真实的 Excel 日期。

例如，在单元格 A1 中，日期 07/05/2025 是以文本写入。要解决，使用：

=DATEVALUE(A2)

这会返回该日期的序列号。然后再用上文介绍的选项将其格式化回日期。

使用 DATEVALUE() 将文本转换为日期。作者供图。

2) 分列工具

您还可以使用“分列”功能将文本转换为日期。步骤如下：

• 选中包含文本日期的列。
• 转到数据选项卡，单击分列。
• 选择分隔符号，单击下一步。
• 选择分隔符，单击下一步。
• 选择日期，并挑选格式（如 MDY）。
• 选择要显示结果的目标区域，单击完成。

使用“分列”向导将文本转换为日期。作者供图。

就这样，您会看到 Excel 立刻把所有文本条目转换成了真实日期。

持续的格式难题

有时即便尝试了这些方法，Excel 仍拒绝将数据视为正确的日期。我们把这些称为“顽固的文本日期”。

常见原因包括：

• 分隔符不对（当 Excel 期望斜杠时却用了连字符）。
• 系统的区域设置与导入数据的格式不匹配。
• 单元格里有多余的空格或不可见字符。

解决方法：

使用 TRIM() 函数移除隐藏空格：

=TRIM(A1)

或在需要时，用 SUBSTITUTE() 将连字符替换为斜杠：

=DATEVALUE(SUBSTITUTE(A1,"-","/"))

假设 Excel 无法识别“5th July 2025”为日期。您可以像这样去掉其中的 th：

=SUBSTITUTE(A1,"5th","5")

将日期转换为数字

每个 Excel 日期的背后都有一个序列号。Excel 利用它来进行日期运算，例如计算两个日期之间的天数。

要查看这个数字：

• 单击日期单元格。
• 转到开始选项卡的数字组。
• 选择常规或数值。

日期 July 5, 2025 将显示为 45843。

您也可以用公式而非手动查找：

• =DATEVALUE("7/5/2025") 显示为 45843。

• =TIMEVALUE("10:30 AM") 返回一个小数值，例如 0.4375，因为时间是一天的一部分。

将日期转换为数字。作者供图。

管理日期的实用 Excel 公式

Excel 内置的日期公式能帮您节省时间，让表格更灵活。无论是跟踪截止日期还是准备报告，这些公式都能帮助自动化您的工作流程。

常用日期相关公式

以下是一些最常用的日期函数：

日期相关内置公式。作者供图。

数据准备与展示技巧

在 Excel 中，清晰与一致至关重要，尤其当您的文件将被共享或用于报告时。几个简单习惯就能大幅避免混淆与公式错误。

以下是一些快速建议，帮助您保持日期的干净、可读与可靠：

• 始终使用四位数年份（如用 2025 而非 25），以避免歧义或误判。

• 在整张表中始终使用一致的日期格式，例如 mm/dd/yyyy 或 dd-mmm-yyyy。

• 在仪表板与图表中使用 TEXT() 创建友好的标签。

• 导入杂乱数据时，用一列保留原始值，另一列用公式进行转换或格式化。

配置系统级日期设置

Excel 使用您电脑的系统日期设置来决定日期的显示方式。通过调整这些设置，您可以控制 Excel 的默认显示，无论是个人使用还是在整个组织范围内。

覆盖区域默认值

Excel 会使用 Windows 的区域设置来格式化日期。如果您希望 Excel 默认以特定格式显示日期，可以调整系统设置。

操作方法：

1. 从开始菜单打开控制面板。
2. 转到时钟和区域（某些系统名称可能不同），然后选择区域。
3. 选择区域，并在下拉列表中选择日期格式。
4. 完成后单击应用。

不过，如果您不想使用预定义格式，而是希望自行自定义，请使用其他设置选项。

1. 在日期选项卡下，设置您偏好的短日期和长日期格式。
2. 单击应用。

在 Windows 中更改区域设置。作者供图

带星号与不带星号的格式

当您在 Excel 中选择日期格式（通过设置单元格格式）时，会注意到有些格式前面带有星号（*），如下所示：

默认日期格式。作者供图。

这些带星号的格式与 Windows 区域设置关联，如果您更改系统格式，它们会自动更新。

而不带星号的格式是固定的——无论系统设置怎么变，它们都不会改变。

注意：如果您希望文件能随用户的系统设置自动调整，请使用带星号的格式；如果您希望日期外观在任何环境下都保持一致，请使用不带星号的格式。

企业级部署策略

在组织中，一致性很重要，尤其当团队分布在不同地点或系统上工作时。您不希望同一份报告里，有的团队用 MM/DD/YYYY，有的用 DD/MM/YYYY。

以下是 IT 团队或 Excel 高级用户可以采用的一些方法：

1. 使用组策略控制系统日期设置。

组策略是 Windows 的一项功能，允许管理员在公司或网络内的所有电脑上统一控制设置。管理员无需逐个用户手动修改，而是可以为所有人自动应用或锁定设置。

例如，您的公司位于英国，您希望所有 Excel 用户都以 DD/MM/YYYY 的格式查看和输入日期。

通过组策略，管理员可以：

• 在所有员工电脑上设置系统日期格式。
• 锁定该设置，防止用户误改。
• 确保 Excel 默认使用该格式（通过与星号格式的关联）。

2. 提供标准化的 Excel 模板。

您还可以创建并共享包含以下内容的 Excel 模板：

• 预设日期格式
• 示例性日期格式说明
• 受保护的单元格格式，防止误改

这是一种简单而有效的方法，确保团队成员在同样的结构与格式规则下开始工作。

排查常见日期格式问题

以下是一些最常见的日期相关问题及其解决方法。

显示与适配错误

如果您在单元格中写入日期，却显示为 #####，这表示列宽太窄，无法显示完整日期值，需要扩大列宽。

方法是双击列标题右边缘，或手动拖动。

扩展列宽。作者供图。

另一个问题是负数意外变成奇怪的日期。这通常是因为单元格之前设置了日期格式，当输入负数（如 -1）时就被当成日期了。

解决方法是在开始选项卡 > 数字格式下拉菜单中，将单元格格式改回常规或数值。

修复被显示为日期的负数。作者供图。

导入与复制粘贴差异

如上所述，Excel 可能会将您的日期当作纯文本而非真实日期值。

您可以通过以下方式修复：

• 使用分列功能（在数据选项卡下）将文本转换为实际日期。

• 若格式可读（如 07/05/2025），使用 =DATEVALUE()。

当我们在不同 Excel 文件之间复制粘贴（尤其是 Windows 与 Mac 之间）时，可能出现的另一个问题是日期前后错位 4 年。这是因为一个文件使用 1900 日期系统，另一个使用 1904 日期系统。

解决方法：转到文件 > 选项 > 高级 > “计算此工作簿时”。然后根据来源勾选或取消勾选“使用 1904 日期系统”。

区域与系统不匹配错误

当我们与不同国家/地区的人协作时，格式可能会引起混淆，比如一个人的系统使用 MM/DD/YYYY，另一个使用 DD/MM/YYYY。

如果有人输入 04/05/2025，在美国系统上 Excel 可能读作 4 月 5 日，而在英国系统上则是 5 月 4 日。

解决方法：

• 使用带月份名称的清晰完整格式，如 5 July 2025，以避免歧义。

• 或使用 TEXT() 函数创建清晰、固定的显示，例如：

=TEXT(A1, "dd-mmm-yyyy")

这会显示为 05-Jul-2025，在各系统间更具兼容性。

结论与最佳实践

Excel 的日期格式小毛病会在不经意间扰乱协作与报告，尤其当您跨系统（Windows 与 Mac）、语言或时区工作时。

为避免意外，请以全球化流程为前提进行设计。使用清晰、与区域无关的格式（如 ISO：yyyy-mm-dd），并在工作簿中直接记录格式选择。这一小步很有价值。

简而言之，把日期格式当作一种“设计决定”而非“设置项”。在这里多一点有意为之，能让您的表格更干净、更易协作。