![Excel 文本转换为日期格式](https://linux22.com/image/1449405/Excel%20%E6%96%87%E6%9C%AC%E8%BD%AC%E6%8D%A2%E4%B8%BA%E6%97%A5%E6%9C%9F%E6%A0%BC%E5%BC%8F.png)
我有一批包含几列的数据,其日期格式如下:
2014 年 5 月 31 日。
我正在尝试找到一种方法将其转换为有用的日期格式 05/31/2014。有什么想法吗?
答案1
实现这一点需要几个步骤。这是使用公式和辅助单元格来实现的一种方法。如果您需要在没有辅助单元格的情况下执行此操作(即您不想要额外的列),那么您应该提到这一点,我可能会编写一些 VBA 来完成这项任务。
第一个任务是将日期分成 3 个部分:年、月、日。一旦确定了这三个部分,再将它们拼凑起来就很简单了。
当然,这是假设你的日期总是遵循 MMM-dd-yyyy 的相当愚蠢的美国日期格式©。
这是使用 Microsoft 页面完成的使用函数在列之间拆分文本举个例子。
拆分月份:
这是使用search
函数查找第一个月份-
并使用拆分工具Left
将其切掉来实现的。=LEFT(A1, SEARCH("-",A1,1)-1)
May
从您的示例中返回。拆分日期:
类似,但需要更复杂的函数使用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
从您的示例中返回。拆分年份:
我们需要的最后一个工具是RIGHT
工具。=RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))
这只是获取字符串的长度,然后搜索两次并返回左边(或右边)的所有内容。
现在的问题是,您仍然以无法使用的文本格式保存月份。为了使其可用,我们需要使用该DATEVALUE
工具对其进行转换。此工具不会单独接受月份,但会接受字符串MMM YY
。我们可以做的是,在将年份连接到月份文本上后,将其解析为数字。
将月份获取为数值:
为了论证的目的,假设我们在单元格 B1 中存储了第一个函数。
DATEVALUE(B1 &" 01")
将返回 Excel 内部使用的日期值字符串,然后我们可以将其包装在函数中MONTH
以返回月份的数值:MONTH(DATEVALUE(B1 &" 01"))
然后,我们可以将所有这些合并到一个
DATE(year,month,day)
函数中,从而将其转换为 Excel 可以使用的真实日期。月份在单元格 B1 中,日期在单元格 C1 中,年份在 D1 中,遵循 Stupid American Date Format™:DATE(D1, B1, C1)
然后将返回
23/05/2014
牢房。如果您想要更加精致,您可以继续将上述所有公式合并为一个。
=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)
谢谢!