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

Microsoft Excel不仅仅只是我们工作的应用程序。有时，我们需要在Microsoft Excel工作表中有效地从.csv文件或其他外部来源导入各种日期。当这种情况发生时，日期将被导出为文本条目，尽管它们看起来与日期相似，但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中计算日期时，使用不同的可用日期函数来返回结果。日期函数通常是表示日期的序列号。

• 例如： 如果=TODAY()+7返回类似44286的数字，而不是今天之后7天的日期，这并不意味着输入的公式有误。相应的单元格格式设置为 常规或文本 ，而应该是 日期 。

我们必须更改单元格的数字格式，以有效地将这些序列号转换为日期。因此，对于这一点，我们将在 主页 选项卡上的 数字格式 框中选择日期。

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

如何将一个8位数字转换为Excel中的日期

在Microsoft Excel中，存在一种常见的情况，即将特定日期输入为8位数字，例如10032016。我们需要将其转换为Excel可以有效识别的日期值（10/03/2016）。

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

为了将这样的数字转换为日期，我们将使用DATE函数与RIGHT、LEFT和MID函数相结合。不幸的是，只有制作一种通用公式才可能实现这一点，因此在某些情况下可以工作，因为原始数字可以以各种格式输入。

• 例如：

Number	Format	Date
10032016	ddmmyyyy	10-Mar-2016
20160310	yyyymmdd
20161003	yyyyddmm

我们将简要介绍将这些数字转换为日期的一般方法，然后提供一些公式作为示例。

首先，我们要记住Microsoft Excel Date函数参数的顺序：

DATE (年，月，日)

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

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

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&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函数主要将文本日期转换为序列号，我们必须将该数字转换为日期形式，只需将日期格式应用于它即可。

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

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

VALUE函数的语法是可以在Microsoft Excel中使用的，如下所示：

VALUE (text)

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

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

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

除了使用特定类型的Microsoft Excel函数，如VALUE和DATEVALUE，我们还可以通过执行简单的数学运算来强制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函数应该没有问题将文本字符串转换为日期。并且以相同的方式，我们可以修复包含任何其他分隔符（例如空格）的日期。

如果我们更喜欢一种公式解决方案，可以使用微软Excel的SUBSTITUTE函数来代替“替换全部”来切换我们的分隔符为斜杠。

假设文本字符串位于A列中，一个SUBSTITUTE公式可能如下所示：

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

其中A1是一个文本日期，”.”是我们当前字符串之间分隔符。

让我们将这个SUBSTITUTE函数嵌入到VALUE公式中：

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

然后使用一个单一的公式将文本字符串分别转换为日期。

发现Microsoft Excel的DATEVALUE和VALUE函数非常强大，但两者都有其限制。

• 例如： 如果我们尝试转换复杂的文本字符串，例如Thursday, January 01, 2015，那么这两个函数都无法在任何方面帮助我们。幸运的是，在Microsoft Excel中，有非公式解决方案可以处理这个任务。下面将对此进行解释。

文本分列向导

在Microsoft Excel中，如果我们是非公式用户类别，那么一个被称为文本分列的常用Excel功能将会非常有用，它可以处理各种简单的文本日期，示例如下所示。

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

如果我们想要转换为日期的文本字符串可能是以下任何一种：

1.1.2015

1.2015

01 01 2015

2015/1/1

我们不需要使用这些公式，也不需要导入或导出任何东西。只需要5个简单的步骤：

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

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

步骤2： 然后切换到“数据”选项卡的“数据工具”组，点击“文本分列”选项。

步骤3： 在“转换文本到列”向导的第一步中，选择“分隔符”并点击“下一步”。

第四步： 在向导的第2步中，我们将取消选中所有分隔符框，并依次点击 下一步 选项。

步骤 5: 现在，在最后一步，我们将选择出现在 列数据格式 下方的日期，并选择与我们的日期对应的格式，然后点击 完成按钮。

在这个具体的例子中，我们正在转换格式为”01 02 2015″（月份 日 年）的特定文本日期，所以我们将从下拉框中选择 MDY 。

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

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

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

Thursday, January 01, 2015

January 01, 2015, 3 PM

那么我们需要更多的努力，使用 文本分列 向导和Excel的日期函数。

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

步骤2： 然后，我们将在 数据 选项卡的 数据工具 组中点击 文本分列 按钮。

步骤3： 在 将文本分列向导 的第1步中，我们将选择 分隔符 ，然后点击 下一步 。

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

• 例如： 如果我们要转换由逗号和空格分隔的字符，如”Thursday, January 01, 2015″，我们应该分别选择逗号和空格作为分隔符。

此外，如果我们的特定数据中有多余的空格，选择“将连续的定界符视为一个”选项也是有道理的，可以忽略这些额外的空格。

最后，查看“数据预览”窗口，验证文本字符串是否正确地分割到列中。然后我们将点击“下一步”选项。

第五步： 在向导的第3步中，确保“数据预览”部分的所有列格式为“常规”。如果不是的话，我们需要点击某一列并在“列数据格式”选项下选择“常规”。

如果不需要某一列，然后点击它并选择“不导入列（跳过）”。

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

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

正如上面的屏幕截图所示，我们只是跳过了星期几的第一列，并将其他数据分成了3列（以一般格式），然后我们将从单元格C2开始插入这些列。

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

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

DATE(年, 月, 日)

在我们的情况下， 年 在E列中，而 日 在D列中，没有问题。

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

=MONTH(序列号)

为了让MONTH函数理解它处理的是一个日期，我们将其写成这样:

**=MONTH(1 &C2) **

其中C2包含月份的名称，我们的情况下为”January”。在日期之前添加”1&”以连接日期( January 01)，这样MONTH函数就可以将其转换为相应的月份数字。

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

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