Excel带AM/PM的日期时间文本转日期时DATEVALUE、TEXT函数报错

常见出错原因 #

• DATEVALUE() 仅支持解析纯日期字符串，无法处理附带时间、AM/PM标识的复合文本，直接调用会返回#VALUE!错误
• TEXT() 函数的作用是将已识别的日期数值转换为指定格式的文本，无法反向将文本型日期转为可计算的日期值，操作逻辑完全相反
• 源文本附带前后冗余空格、Excel默认区域格式不识别美式「月/日/年」结构，也会导致解析失败

可落地操作方案 #

方法1：公式转换（适配任意带冗余空格的源文本） #

在目标单元格输入以下公式，回车后下拉即可：
=DATEVALUE(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)) + TIMEVALUE(MID(TRIM(A1),FIND(" ",TRIM(A1))+1,100))
公式逻辑：

1. TRIM(A1) 清除源文本前后的多余空格
2. 按空格拆分出日期、时间两个独立片段，分别用DATEVALUE()、TIMEVALUE()解析为日期、时间对应的序列值
3. 两个序列值相加即为完整的可计算日期时间数值

如果确认源文本无多余空格，可使用更简便的公式：
=--TRIM(A1)
双负号的作用是将文本型日期强制转换为数值型日期序列值。

方法2：无公式批量转换 #

1. 选中需要转换的日期列
2. 点击顶部菜单栏「数据」-「分列」
3. 前两个配置页直接点击「下一步」，到第三步时，列数据格式选择「日期」，下拉选项选中「MDY」（匹配源文本「月/日/年」的结构，避免把2/10识别为10月2日）
4. 点击「完成」，Excel会自动识别文本中的AM/PM时间标识，批量转为可计算的标准日期格式

后续格式调整 #

公式或分列得到的结果为日期序列值，选中对应单元格后右键点击「设置单元格格式」，选择需要的日期/时间展示样式即可，可正常用于日期差计算、筛选、排序等操作。

内容的提问来源于stack exchange，提问作者Jerr567