☰

Excel 文本转换为日期格式

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
Excel 文本转换为日期格式

我有一批包含几列的数据，其日期格式如下：

2014 年 5 月 31 日。

我正在尝试找到一种方法将其转换为有用的日期格式 05/31/2014。有什么想法吗？

答案1

实现这一点需要几个步骤。这是使用公式和辅助单元格来实现的一种方法。如果您需要在没有辅助单元格的情况下执行此操作（即您不想要额外的列），那么您应该提到这一点，我可能会编写一些 VBA 来完成这项任务。

第一个任务是将日期分成 3 个部分：年、月、日。一旦确定了这三个部分，再将它们拼凑起来就很简单了。

当然，这是假设你的日期总是遵循 MMM-dd-yyyy 的相当愚蠢的美国日期格式©。

这是使用 Microsoft 页面完成的使用函数在列之间拆分文本举个例子。

  1. 拆分月份：
    这是使用search函数查找第一个月份-并使用拆分工具Left将其切掉来实现的。

    =LEFT(A1, SEARCH("-",A1,1)-1)

    May从您的示例中返回。

  2. 拆分日期：
    类似，但需要更复杂的函数使用MID。我们需要找到第一个和第二个-，然后从第二个中减去第一个的位置，以获得所需文本的长度，结果是一个相当长的函数：

    =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

    返回SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)长度，而SEARCH("-",A1,1)+1仅获取第一个位置。该MID工具将它们剪切出来并23从您的示例中返回。

  3. 拆分年份：
    我们需要的最后一个工具是RIGHT工具。

    =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

    这只是获取字符串的长度，然后搜索两次并返回左边（或右边）的所有内容。

现在的问题是，您仍然以无法使用的文本格式保存月份。为了使其可用，我们需要使用该DATEVALUE工具对其进行转换。此工具不会单独接受月份，但会接受字符串MMM YY。我们可以做的是，在将年份连接到月份文本上后，将其解析为数字。

  1. 将月份获取为数值：
    为了论证的目的，假设我们在单元格 B1 中存储了第一个函数。
    DATEVALUE(B1 &" 01")将返回 Excel 内部使用的日期值字符串，然后我们可以将其包装在函数中MONTH以返回月份的数值：

    MONTH(DATEVALUE(B1 &" 01"))

  2. 然后，我们可以将所有这些合并到一个DATE(year,month,day)函数中，从而将其转换为 Excel 可以使用的真实日期。月份在单元格 B1 中，日期在单元格 C1 中，年份在 D1 中，遵循 Stupid American Date Format™：

    DATE(D1, B1, C1)

    然后将返回23/05/2014牢房。

  3. 如果您想要更加精致，您可以继续将上述所有公式合并为一个。

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

答案2

另一种简单的方法是采用如下字符串2015 年 5 月 31 日并将其转换为2015 年 5 月 31 日。这种转换将允许我们使用DATEVALUE()公式。

因此，A1， 在B1进入：

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

在此处输入图片描述

答案3

我知道这个问题已经得到解答，我感谢大家的意见。我想添加这个其他选项，以防对任何人有帮助：

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

这会将日期从 2014 年 5 月 31 日格式转换为 2014 年 5 月 31 日格式。请注意，在我的特定情况下，月份仅输入了三个字母（例如 Jul）

谢谢！

相关内容