我有一个这种特定格式的字符串:
DD/MMM/YY HH:MM AM/PM
例如
03/Nov/14 9:00 AM
我不知道该怎么做计算将其识别为日期,因此我必须对日期字符串执行以下操作B2:
=DATEVALUE( 连接( 2000+中(B2,8,2), “-” 如果(MID(B2,4,3)=“一月”,1, 如果(MID(B2,4,3)=“二月”,2, 如果(MID(B2,4,3)=“三月”,3, 如果(MID(B2,4,3)=“4月”,4, 如果(MID(B2,4,3)=“5月”,5, 如果(MID(B2,4,3)=“6月”,6, 如果(MID(B2,4,3)=“7月”,7, 如果(MID(B2,4,3)=“8月”,8, 如果(MID(B2,4,3)=“9月”,9, 如果(MID(B2,4,3)=“10月”,10, 如果(MID(B2,4,3)=“11月”,11, 如果 (MID(B2,4,3)="12 月",12,"" ))))))))))))), “-” 中值(B2,1,2) ) ) + 时间值( 如果(MID(B2,8,1)=“”,RIGHT(B2,7),RIGHT(B2,8)) )
或者在一行中:
=DATEVALUE(CONCATENATE(2000+MID(B2,8,2),"-",IF(MID(B2,4,3)="Jan",1,IF(MID(B2,4,3)="Feb",2,IF(MID(B2,4,3)="Mar",3,IF(MID(B2,4,3)="Apr",4,IF(MID(B2,4,3)="May",5,IF(MID(B2,4,3)="Jun",6,IF(MID(B2,4,3)="Jul",7,IF(MID(B2,4,3)="Aug",8,IF(MID(B2,4,3)="Sep",9,IF(MID(B2,4,3)="Oct",10,IF(MID(B2,4,3)="Nov",11,IF(MID(B2,4,3)="Dec",12,"")))))))))))),"-",MID(B2,1,2)))+TIMEVALUE(IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8)))
然后将包含公式的单元格类型更改为日期并选择所需的格式。
总而言之,这是一个相当复杂的过程。有没有更自动化的方法?
答案1
您可以使用某种“查找表”将月份名称转换为日期(此解决方案基于针对相反操作提出的解决方案 - 将数字转换为月份名称)
只需将月份名称一个接一个地放入电子表格中的一列中,然后在这些单元格上定义命名范围(例如)。现在,您可以使用以下结果Monthnames
确定月份的数字:=MATCH("Dec",Monthnames,0)
12
有了这样的表,您就可以IF
用一个MATCH
调用来替换语句:
=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MATCH(MID(A2,4,3),Monthnames,0),"/",MID(A2,1,2)))
或者在多行上:
=DATEVALUE( CONCATENATE( MID(A2,8,2), "/", MATCH( MID(A2,4,3), Monthnames, 0 ), "/", MID(A2,1,2) ) )
上述公式根据示例字符串构造一个有效的日期值:
答案2
使用 MONTH 和虚拟 dd、yy 值提取月份值:
=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MONTH("1"&MID(A2,4,3)&"1"),"/",MID(A2,1,2)))
答案3
尝试在日期中添加零。对于从数据文件导入的数据,当日期为文本格式(即值前面有一个撇号)时,此方法对我有用。