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

分析业务数据通常需要使用 Excel 中的日期值来回答诸如“我们今天赚了多少钱”或“这与上周的同一天相比如何?”等问题。 当 Excel 无法将这些值识别为日期时,这可能会很困难。

不幸的是,这并不罕见,尤其是当多个用户键入此信息、从其他系统复制和粘贴以及从数据库导入时。

在本文中,我们将描述四种不同的场景以及将文本转换为日期值的解决方案。

包含句号/句号的日期

初学者在 Excel 中输入日期时最常犯的错误之一可能是使用句号分隔日、月和年。

Excel 不会将此识别为日期值,并将继续将其存储为文本。 但是,您可以使用查找和替换工具解决此问题。 通过用斜杠 (/) 替换句号,Excel 将自动将值标识为日期。

选择要对其执行查找和替换的列。

单击主页 > 查找和选择 > 替换 — 或按 Ctrl+H。

在“查找和替换”窗口中,在“查找内容”字段中键入句点 (.),在“替换为”字段中键入斜杠 (/)。 然后,单击“全部替换”。

所有句点都转换为斜线,Excel 将新格式识别为日期。

如果您的电子表格数据定期更改,并且您需要针对这种情况的自动化解决方案,您可以使用 替换函数.

=VALUE(SUBSTITUTE(A2,".","https://www.wdzwdz.com/"))

SUBSTITUTE 函数是一个文本函数,因此不能单独将其转换为日期。 VALUE 函数将文本值转换为数值。

结果如下所示。 该值需要格式化为日期。

您可以使用“主页”选项卡上的“数字格式”列表来执行此操作。

这里的句号分隔符示例是典型的。 但是您可以使用相同的技术来替换或替换任何分隔符。

转换 yyyymmdd 格式

如果您收到如下所示格式的日期,则需要采用不同的方法。

这种格式在技术上是相当标准的,因为它消除了关于不同国家如何存储其日期值的任何歧义。 但是,Excel 最初不会理解它。

对于快速手动解决方案,您可以使用 Text to Columns。

选择需要转换的值范围,然后单击数据 > 文本到列。

将出现“文本到列”向导。 在前两步单击“下一步”,进入第三步,如下图所示。 选择日期,然后从列表中选择单元格中使用的日期格式。 在本例中,我们处理的是 YMD 格式。

如果您想要一个公式解决方案,那么您可以使用 Date 函数来构造日期。

这将与文本函数 Left、Mid 和 Right 一起使用,以从单元格内容中提取日期的三个部分(日、月、年)。

  如何离线查看地图

下面的公式使用我们的样本数据显示了这个公式。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

使用这些技术中的任何一种,您都可以转换任何八位数字值。 例如,您可能会收到 ddmmyyyy 格式或 mmddyyyy 格式的日期。

DATEVALUE 和 VALUE 函数

有时问题不是由分隔符引起的,而是由于它存储为文本而具有尴尬的日期结构。

以下是各种结构中的日期列表,但我们都可以将它们识别为日期。 不幸的是,它们被存储为文本,需要转换。

对于这些场景,很容易使用多种技术进行转换。

对于本文,我想提两个函数来处理这些场景。 它们是 DATEVALUE 和 VALUE。

DATEVALUE 函数将文本转换为日期值(可能已经看到),而 VALUE 函数将文本转换为通用数字值。 它们之间的差异很小。

在上图中,其中一个值也包含时间信息。 这将展示函数的细微差别。

下面的 DATEVALUE 公式会将每一个转换为日期值。

=DATEVALUE(A2)

注意时间是如何从第 4 行的结果中删除的。这个公式严格地只返回日期值。 结果仍需要格式化为日期。

以下公式使用 VALUE 函数。

=VALUE(A2)

此公式将产生相同的结果,但第 4 行除外,其中时间值也保持不变。

然后可以将结果格式化为日期和时间,或日期以隐藏时间值(但不能删除)。