将带有日期的文本字段转换为日期格式的字段

将带有日期的文本字段转换为日期格式的字段

我有一个日期格式如下的列:

July 14, 2015 11:02

我如何将其转换为 DATE 格式以便于查询?(如果这样更容易,我不一定需要时间?)

编辑 看起来不错,但我仍然看到错误

编辑 2 月份计算不正确

在此处输入图片描述

答案1

您的值在单元格中A1,此公式将分离日期部分:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

丢弃最后一个空格后的所有内容(时间部分)

将其转换为真实日期, 使用:

=DATEVALUE(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

在此处输入图片描述

要“就地”进行这种转换需要一个宏。

编辑#1:

要查找数据中的任何“垃圾”字符,请在单元格中输入示例日期A1.然后在B1进入:

=MID($A$1,ROW(),1)

并复制下来。然后在C1进入:

=CODE(B1)

并复制下来。你应该看到:

在此处输入图片描述

编辑#2:

鉴于您的数据A1,此公式:

=--MID(A1,FIND(", ",A1)+2,4)

将返回年份(2015 年). 此公式:

=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12})

将返回月份(7). 此公式:

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

将返回的那一天(14)

综合起来:

=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))

这个公式避免了与DATEVALUE()

编辑#3:

正如您所发现的,月份数组必须是已排序,因此请用这个表示月份:

=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})

这是“最终”公式:

=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))

编辑以删除公式中的空格(编辑必须是 6 个字符,因此有此描述)

相关内容