如何用Excel将两种日期时间格式统一为mm/dd/yyyy hh:mm格式？

问题说明 #

雨量计生成的CSV数据存在两种日期时间格式：

• 格式1：mm/dd/yyyy hh:mm（24小时制，Excel可自动识别为日期类型）
• 格式2：mm/dd/yy hh:mm:ss AM/PM（12小时制，Excel识别为文本类型）

需通过纯Excel操作实现格式统一，无需编程经验，适配非科研人员的“即插即用”需求。

解决方案 #

方法1：公式一键转换（适合快速处理现有数据） #

假设日期时间数据在A列，在空白列（比如B列）第一个单元格输入以下公式，下拉填充即可：

=IFERROR(VALUE(A1), DATEVALUE(LEFT(A1,6)&"20"&MID(A1,7,2)) + TIMEVALUE(MID(A1,10,8)&RIGHT(A1,2)))

公式说明

• IFERROR(VALUE(A1), ...)：先尝试直接转换A列内容，能识别为日期的（格式1）直接保留
• DATEVALUE(LEFT(A1,6)&"20"&MID(A1,7,2))：提取格式2的日期部分，把两位年份补全为四位（如22转为2022），转换为日期值
• TIMEVALUE(MID(A1,10,8)&RIGHT(A1,2))：提取格式2的时间和AM/PM部分，转换为时间值
• 两者相加得到完整日期时间值，最后将B列单元格格式设置为mm/dd/yyyy hh:mm即可匹配格式1

方法2：Power Query批量处理（适合重复使用的“即插即用”模板） #

你已通过Excel>Get Data>From Text导入数据，可直接在Power Query编辑器中添加转换步骤，保存后下次导入数据可一键套用：

1. 在Power Query编辑器中选中日期时间列
2. 点击「转换」选项卡 → 「数据类型」→ 选择「日期/时间」，此时格式1会被正确识别，格式2会显示错误
3. 点击列标题旁的错误提示按钮 → 选择「替换错误」
4. 在弹出对话框中输入以下公式（替换「你的日期列名」为实际列名，如Column1），点击确定：

   DateTime.FromText([你的日期列名], [Format="MM/dd/yyyy hh:mm:ss tt", Culture="en-US"])
   

5. 转换完成后点击「关闭并上载」，数据会自动统一为日期时间类型，最后设置单元格格式为mm/dd/yyyy hh:mm即可

验证方法 #

选中转换后的列，右键选择「设置单元格格式」→「自定义」，输入mm/dd/yyyy hh:mm，所有行都会显示为统一格式，且能正常参与日期相关计算。

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