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

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

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

已经知道，在将一定量的数据导入Microsoft Excel时，格式化日期通常会出现问题。导入的条目看起来可能与普通Excel日期非常相似，但它们不像通常的日期一样运作。

此外，Microsoft Excel将所有这些条目视为文本，这意味着我们不能正常地按日期对表进行排序。我们也不能在公式、数据透视表、图表或任何其他主要识别日期的Microsoft Excel工具中使用这些“文本日期”。

以下是一些标志，可帮助我们确定给定条目是日期还是文本值：

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

众所周知，所有可用于将文本转换为日期的Microsoft Excel函数将返回一个数字；因此，首先让我们更详细地查看将数字转换为日期。

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

例如，1-Jan-1900是可以存储为数字1的日期，2-Jan-1900是可以存储为2的另一个日期，而1-Jan-2015存储为42005.

在Microsoft Excel中计算日期时，由Microsoft Excel中提供的不同日期函数返回结果。日期函数通常是表示日期的序列号。 * 比如： 如果=TODAY()+7返回类似于44286的数字而非当前日期往后七天的日期，那并不代表该公式有误。只是给定的单元格格式为常规或文本，而应该改为日期。

我们需要更改单元格数字格式以将这些序列号有效地转换为日期。因此，我们会在主页选项卡的数字格式框中挑选出日期。

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

在Microsoft Excel中，一个非常常见的情况是将日期作为八位数字输入，比如10032016。我们需要将其转换为Excel可以有效识别的日期值（即10/03/2016）。

此外，在此情况下更改单元格格式为日期则无效，结果将为##########。

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

我们将解释将这些数字转换为日期的一般方法，然后提供几个公式作为示例。

对于初始用户，我们将了解Microsoft Excel Date函数参数的顺序：

DATE(year, month, and day)

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

最后，我们将所有上述内容嵌入相应的Date函数中，将获得一个在Excel表格中将号码转换为日期的公式：

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

以下截图通常有效地演示了这一点和其他几个公式的动作。

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

无论我们在单独的 Microsoft Excel 文件中发现的文本日期是什么，我们都希望将这些文本字符串转换为普通的 Excel 日期，以便在我们的公式中容易地引用它们并执行各种计算。而且在 Microsoft Excel 中通常有几种方法可以有效处理这个任务。

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

语法：

Microsoft Excel DATEVALUE 的语法如下：

DATEVALUE(date_text)

因此，上述给出的公式可以有效地用于将文本值转换为日期，它非常简单，即 =DATEVALUE(A1)，其中 A1 是一个分别存储为文本字符串的日期的单元格。

因为 Microsoft Excel DATEVALUE 函数主要将文本日期转换为序列号，我们只需将 DATE 格式应用于该数字即可使其看起来像一个日期。

与 DATEVALUE 相比，Microsoft Excel 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 的查找和替换工具，将我们的分隔符替换为斜杠 (/)。

特定的DATEVALUE函数应该没有问题将文本字符串转换为日期。同样，我们可以修正包含任何其他分隔符，例如空格的日期。

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

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

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

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

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

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

通过单个公式，将文本字符串转换为日期。

可以看到，Microsoft Excel的DATEVALUE和VALUE函数非常强大，但两者都有其限制。

在Microsoft Excel中，如果我们是非公式用户类型，则长期存在的Excel功能Text to Columns将非常有用，并且可以处理各种简单的文本日期，例如示例1以及在示例2中显示的多部文本字符串；

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

如果要转换为日期的文本字符串可能如下所示：

1.1.2015

1.2015

01 01 2015

2015/1/1

我们不需要这些公式，也不需要导入或导出任何东西。需要的只是5个快速步骤：

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

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

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

步骤3：在“转换文本为列向导”的第1步中，我们将选择“Delimited”并单击下一步。

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

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

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

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

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

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

Thursday，January 01，2015

January 01，2015，3 PM

然后我们必须付出更多的努力，并且使用Text to Columns向导和Excel DATE函数。

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

步骤2：然后，我们将单击数据选项卡上的“文本到列”按钮，数据工具组。 步骤3：在“文本分列向导”的第一步中，我们将选择“分隔符分隔”并单击“下一步”。

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

如果我们的特定数据中有额外的空格，则选择“将连续的分隔符视为一个”选项忽略它们也是有意义的。

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

步骤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&”是为了连接日期（1月1日），以便MONTH函数可以将其转换为相应的月份号码。

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

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