如何将Excel中常规格式MDY日期自动转换为英国DMY标准日期格式

错误原因说明 #

你使用DATE()函数得到错误结果的核心原因是：当前看似显示为11/08/2021的单元格实际已经被Excel识别为日期序列号，而非纯文本字符串。RIGHT/LEFT/MID这类文本函数操作的是日期序列号的数值位，而非你肉眼看到的日期文本位，自然会得到完全错误的结果。

解决方案 #

1. 公式快速转换法（兼容所有Excel版本）

如果确认原始数据为MDY（月/日/年）格式，先用TEXT函数把日期序列号转为你看到的文本格式，再嵌套进日期转换函数即可：

• 简洁版公式：

  =DATEVALUE(TEXT(A1,"mm/dd/yyyy"))
  

• 兼容极端场景的完整版公式：

  =DATE(VALUE(RIGHT(TEXT(A1,"mm/dd/yyyy"),4)),VALUE(LEFT(TEXT(A1,"mm/dd/yyyy"),2)),VALUE(MID(TEXT(A1,"mm/dd/yyyy"),4,2)))
  

公式输入完成后，把目标单元格格式设置为你需要的日期格式（如yyyy/m/d）即可生效。

2. Power Query自动化批量处理（适合每日重复处理场景）

这个方案第一次配置完成后，后续每次新收到文件只要刷新就能自动出结果，完全无需重复操作：

• 选中原始日期列任意单元格，点击「数据」选项卡 -「从表格/区域」导入Power Query编辑器
• 选中日期列，点击「转换」选项卡 -「数据类型」- 选择「文本」
• 再点击「转换」选项卡 -「日期」-「解析」- 选择「使用区域设置」，区域选择「英语（美国）」（对应美式MDY格式规则）
• 调整列格式为你需要的日期格式后，点击「关闭并上载」到Excel新工作表即可
• 后续收到新的同格式文件，只要把原始数据覆盖到原导入区域，右键点击结果表选择「刷新」就能自动完成数百条日期的转换。

补充说明：如果你的原始数据确实是未被Excel识别的纯文本格式，你原本写的DATE()函数逻辑是正确的，直接使用即可生效。

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