如何在 Microsoft Excel 中将文本转换为日期，将数字转换为日期？

Microsoft Excel 不仅仅是我们正在使用的应用程序。有时我们会处理从 .csv 文件或其他外部源导入到 Microsoft Excel 工作表中的各种日期。当这种情况发生时，日期将被导出为文本条目，即使它们看起来与日期相似，Microsoft Excel 也不会将其识别为日期。

在 Microsoft Excel 中，可以使用各种方法有效地将给定的文本转换为 Excel 工作表中的特定日期，本教程将涵盖所有这些方法，以便我们可以选择最适合我们的数据格式的文本到日期转换技术，然后根据我们对公式或非公式方式的偏好。

1. 如何在 Microsoft Excel 中轻松识别“文本日期”？
2. 如何在 Microsoft Excel 中将给定的数字转换为日期？
3. 如何在 Microsoft Excel 中轻松将文本转换为日期？
   1. “DATEVALUE”函数是什么意思？
   2. VALUE 函数。
   3. 使用数学运算。
   4. 有效转换带有自定义分隔符的文本字符串。
   5. 文本分列向导。
4. 如何在 Microsoft Excel 中以最简单的方式将文本转换为日期？

如何在 Microsoft Excel 中轻松区分普通 Excel 日期和“文本日期”？

众所周知，在将特定数据量导入 Microsoft Excel 时，日期格式经常会出现问题。并且导入的条目看起来与普通的 Excel 日期非常相似，但它们不像通常那样作为日期工作。

此外，Microsoft Excel 将所有此类条目都视为文本，这意味着我们无法按日期正确排序表格；也无法在公式、数据透视表、图表或任何其他主要识别日期的 Microsoft Excel 工具中使用这些“文本日期”。

有几个迹象可以帮助我们确定给定的条目是日期还是文本值，如下所示：

如何在 Microsoft Excel 中将数字转换为日期？

众所周知，所有可用的 Microsoft Excel 函数都将文本转换为日期，返回一个数字；因此，现在让我们先仔细查看如何将数字转换为日期。

Microsoft Excel 将日期和时间存储为序列号，只有单元格格式才会强制将数字显示为日期。

• 例如，1900 年 1 月 1 日的日期可以存储为数字 1，1900 年 1 月 2 日的日期可以存储为数字 2，2015 年 1 月 1 日存储为 42005。

当我们进行 Microsoft Excel 中的日期计算时，结果将由 Microsoft Excel 中提供的不同日期函数返回。日期函数通常是表示日期的序列号。

• 例如：如果 =TODAY()+7 返回的数字是 44286 而不是 7 天后的日期，这并不意味着输入的公式是错误的。，给定的相应单元格格式设置为通用或文本，而它也应该是日期

我们必须更改单元格数字格式才能有效地将这些序列号转换为日期。为此，我们将选择“开始”选项卡上“数字格式”框中的日期。

要应用除默认格式以外的给定格式，我们将选择带有序列号的单元格，然后按 Ctrl+1 打开设置单元格格式对话框。在“数字”选项卡上，我们将选择日期，在类型下选择所需的日期格式，然后单击“确定”按钮。

如何在 Microsoft Excel 中将 8 位数字转换为日期

在 Microsoft Excel 中，一个非常常见的情况是输入了一个 8 位数字的日期，例如 10032016。我们需要将其转换为 Excel 可以有效地识别的日期值（2016 年 3 月 10 日）。

此外，我们可以将单元格格式更改为日期。在这种情况下，这将不起作用 - 我们将分别得到 ########## 结果。

为了将这样的数字转换为日期，我们将使用 DATE 函数结合 RIGHT、LEFT 和 MID 函数。不幸的是，这只能创建一个通用的公式，因为它将在某些场景下工作，因为原始数字可以以各种格式输入。

• 例如

我们将解释将此类数字转换为日期的通用方法，然后提供几个公式作为示例。

首先，我们将记住 Microsoft Excel 日期函数的参数顺序

DATE (年, 月, 日)

因此，我们需要从原始数字中提取年、月和日，并将它们作为相应的参数提供给 Date 函数。

• 例如：让我们看看如何将数字 10032016（存储在单元格 A1 中）转换为日期 2016 年 3 月 10 日。

1. 我们正在提取年份。它是最后 4 位数字，所以我们使用 RIGHT 函数提取最后 4 个字符：RIGHT (A1, 4)。
2. 它们正在提取月份。它是第 3 位和第 4 位数字，所以我们可以使用 MID 函数来获取它们 MID (A1, 3, 2)，其中 3（第二个参数）是起始位置，2（第三个参数）是要有效提取的字符数。
3. 提取日期。它是前 2 位数字，所以我们需要 LEFT 函数返回前 2 个字符 LEFT (A2, 2)。

最后，我们将把所有上述元素嵌入到相应的 Date 函数中，并且我们将得到一个在 Excel 工作表中将数字转换为日期的公式。

=DATE (RIGHT (A1, 4), MID (A1, 3, 2), LEFT (A1, 2))

以下截图通常演示了这一点以及更多公式的实际应用。

我们必须注意上面截图中的最后一个公式（第 6 行）。给定的原始数字日期（161003）主要包含 2 个代表年份的字符（16）。所以，要得到 2016，我们将通过使用以下公式将 20 和 16 连接起来：20&LEFT(A6,2)。如果我们不这样做，Date 函数将默认返回 1916。

如何在 Microsoft Excel 中将文本转换为日期？

每当我们发现单独的 Microsoft Excel 文件中有文本日期时，我们都希望将这些文本字符串转换为普通的 Excel 日期，以便在公式中轻松引用它们来进行各种计算。在 Microsoft Excel 中，通常有几种方法可以有效地处理此任务。

Microsoft Excel DATEVALUE 函数，可用于将文本转换为特定日期

Microsoft Excel 中的 DATEVALUE 函数将文本格式的日期转换为 Microsoft Excel 可以识别为日期的序列号。

语法

Microsoft Excel DATEVALUE 的语法表示如下

DATEVALUE (date_text)

因此，上述公式可以有效地用于将文本值转换为日期，就像=DATEVALUE (A1)一样简单，其中 A1 是包含存储为文本字符串的日期的单元格。

这是因为 Microsoft Excel DATEVALUE 函数主要将文本日期转换为序列号，而我们必须通过应用 DATE 格式来使其看起来像日期。

Microsoft Excel VALUE 函数 - 用于将文本字符串转换为日期

与 DATEVALUE 相比，Microsoft Excel VALUE 函数非常灵活。它可以将看起来像日期或数字的任何文本字符串转换为数字，然后我们可以轻松地将其更改为所需的日期格式。

可以在 Microsoft Excel 中使用的 VALUE 函数的语法如下

text 是一个文本字符串或对包含我们需要转换为数字的文本的单元格的引用。

此外，Microsoft Excel VALUE 函数可以有效地处理日期和时间，然后将其转换为小数部分，正如在下面的屏幕截图中第 6 行所示。

使用数学运算将文本转换为日期

除了使用 VALUE 和 DATEVALUE 等特定类型的 Microsoft Excel 函数外，我们还可以执行简单的数学运算来强制 Excel 为我们进行文本到日期的转换。要实现此目的的必要条件是，该过程不应更改日期值（序列号）。

假设我们的文本日期在单元格 A1 中，我们可以使用以下公式，然后我们将对相应的单元格应用日期格式。

加法：=A1 + 0

乘法：=A1 * 1

除法：=A1 / 1

双重否定：=--A1

根据上图，数学运算可以将日期（第 2 行和第 4 行）、时间（第 6 行）以及格式设置为文本的数字（第 8 行）转换为日期。有时结果甚至会自动显示为日期。

如何将带有自定义分隔符的文本字符串转换为日期？

如果我们的文本日期包含除斜杠（/）或连字符（-）以外的分隔符，Microsoft Excel 函数将无法将其识别为日期，并返回 #VALUE! 错误。

为了解决这个问题，我们可以运行 Microsoft Excel 的查找和替换工具，将我们的分隔符替换为斜杠（/）。

1. 首先，我们将选择所有要转换为日期的文本字符串。
2. 然后，我们将按 Ctrl+H 打开查找和替换对话框。
3. 然后，我们将在“查找内容”字段中输入我们的自定义分隔符，在“替换为”字段中输入斜杠。
4. 完成上述步骤后，我们将单击“全部替换”。

DATEVALUE 函数应该能够轻松地将文本字符串转换为日期。同样，我们可以修复包含任何其他分隔符（例如空格）的日期。

如果我们偏爱公式解决方案，我们可以使用 Microsoft Excel 的 SUBSTITUTE 函数而不是全部替换来将我们的分隔符切换为斜杠。

假设文本字符串在 A 列中，SUBSTITUTE 公式可能如下所示

=SUBSTITUTE (A1, ".", "/")

其中 A1 是一个文本日期，"." 是我们字符串现在分隔的分隔符。

让我们将此 SUBSTITUTE 函数嵌入到 VALUE 公式中。

=VALUE (SUBSTITUTE (A1, ".", "/"))

并使用一个公式将文本字符串转换为日期。

据观察，Microsoft Excel 的 DATEVALUE 和 VALUE 函数功能强大，但它们都有局限性。

• 例如：如果我们尝试转换复杂的文本字符串，例如2015 年 1 月 1 日星期四，那么这两个函数都无法帮助我们。幸运的是，在 Microsoft Excel 中，有一些非公式解决方案可以处理此任务。这将在接下来的部分中进行解释。

文本分列向导

在 Microsoft Excel 中，如果您是喜欢非公式的用户，那么一个长期存在的 Excel 功能，称为文本分列，将会非常有用，它可以处理各种简单的文本日期，如示例 1 所示，以及多部分文本字符串，如示例 2 所示；

示例 1：将简单文本字符串转换为日期

如果我们要转换的文本字符串看起来像下面任何一个

1.1.2015

1.2015

01 01 2015

2015/1/1

我们不需要这些公式，也不需要导出或导入任何内容。只需 5 个快速步骤即可完成，如下所示：

在此示例中，我们将有效地将像 01 01 2015（日、月、年用空格分隔）这样的文本字符串转换为日期。

第 1 步：在我们的 Excel 工作表中，我们将选择要转换为日期的文本条目所在的列。

第 2 步：之后，我们将切换到数据选项卡，“数据工具”组，然后单击“文本分列”选项。

第 3 步：在将文本转换为列向导的第 1 步中，我们将选择“分隔符”，然后单击“下一步”。

第 4 步：现在，在向导的第 2 步中，我们将取消选中所有分隔符复选框，然后单击“下一步”选项。

第 5 步：现在，在最后一步中，我们将选择列数据格式下的“日期”，并选择与我们的日期相对应的格式，然后单击“完成”按钮。

在此示例中，我们正在转换格式为“01 02 2015”（月日年）的特定文本日期，因此我们将从下拉框中选择MDY。

Microsoft Excel 将识别我们的文本字符串为日期，并自动将其转换为我们默认的日期格式，并在单元格中右对齐显示它们。

示例 2：将复杂的文本字符串转换为日期

如果我们的日期由多部分文本字符串表示，例如

2015 年 1 月 1 日星期四

2015 年 1 月 1 日，下午 3 点

那么我们就需要付出更多的努力，并且使用文本分列向导和 Excel DATE 函数。

第 1 步：首先，我们将选择所有需要转换为日期的文本字符串。

第 2 步：然后，我们将单击“数据”选项卡，“数据工具”组中的“文本分列”按钮。

第 3 步：在将文本转换为列向导的第 1 步中，我们将选择“分隔符”，然后单击“下一步”。

第 4 步：在向导的第 2 步中，我们将选择文本字符串包含的分隔符。

• 例如：如果我们正在转换由逗号和空格分隔的字符串，例如“2015 年 1 月 1 日，星期四”，我们应该同时选择分隔符 - 逗号和空格。

选择“将连续分隔符视为一个”选项以忽略多余空格（如果我们的数据中有）也是有意义的。

最后，查看数据预览窗口并验证文本字符串是否已正确拆分为列。然后，我们将单击“下一步”选项。

第 5 步：在向导的第 3 步中，确保“数据预览”部分中的所有列都具有通用格式。如果没有，我们需要单击一列并在列数据格式选项下选择通用。

如果我们不需要某列，则单击该列并选择不导入列（跳过）。

如果我们不想覆盖原始数据，请指定列的插入位置 - 并在“目标”字段中输入左上角的单元格地址。

完成后，我们将分别单击“完成”按钮。

正如上图所示，我们只是跳过了星期几的第一列，并将其他数据拆分为 3 列（以通用格式），之后我们将从单元格 C2 开始插入这些列。

下图显示了结果，原始数据在 A 列中，然后我们将数据拆分到 C、D 和 E 列中。

最后，我们必须使用 DATE 公式组合日期部分。Excel DATE 函数的语法是不言自明的。

DATE (年, 月, 日)

在我们的例子中，年在 E 列，日在 D 列；没问题。

对于月来说比较困难，因为它以文本形式存在，而 DATE 函数通常需要数字。幸运的是，Microsoft Excel 提供了一个特殊的 MONTH 函数，可以将月份名称转换为月份数字。

=MONTH(serial_number)

为了让 MONTH 函数理解它正在处理一个日期，我们将其设置为如下：

=MONTH(1&C2)

其中 C2 包含月份的名称，在本例中为一月。“1&”被添加以连接日期（1 月 01 日），以便 MONTH 函数可以将其转换为相应的月份编号。

现在，让我们将 MONTH 函数嵌入到 DATE 公式中月参数。

=DATE(F2,MONTH(1&D2),E2)