Excel混合类型日期列无法正确格式化问题咨询

问题本质 #

无法调整格式的日期为文本类型存储，不属于Excel可识别的日期序列值，直接修改单元格格式对文本内容无效，需要先统一转换为标准日期值再调整显示格式。

操作方法 #

方法1：分列法（无公式，操作最快） #

• 选中整列日期数据，点击顶部菜单栏「数据」→「分列」
• 前2个步骤直接点击「下一步」，第三步列数据格式选择「日期」，下拉选项选「MDY（月-日-年）」，点击「完成」
• 此时所有数据已转为标准日期值，右键选中列→「设置单元格格式」→「自定义」，输入格式代码：
  • 要横杠分隔就填 mm-dd-yyyy
  • 要斜杠分隔就填 mm/dd/yyyy
• 确认后即可统一显示为你需要的格式

方法2：公式转换法（不改动原列数据） #

在原日期列旁插入空白列，根据你的Excel版本选对应公式下拉填充即可：

• 全版本通用公式：
  =DATE(RIGHT(A1,4),LEFT(A1,FIND(IF(ISNUMBER(FIND("-",A1)),"-","/"),A1)-1),MID(A1,FIND(IF(ISNUMBER(FIND("-",A1)),"-","/"),A1)+1,FIND(IF(ISNUMBER(FIND("-",A1)),"-","/"),A1,FIND(IF(ISNUMBER(FIND("-",A1)),"-","/"),A1)+1)-FIND(IF(ISNUMBER(FIND("-",A1)),"-","/"),A1)-1))

注：公式中A1替换为你原日期列首个单元格的位置

• 高版本Excel/WPS简易公式：
  =TEXT(DATEVALUE(SUBSTITUTE(A1,"-","/")),"mm-dd-yyyy")

注：需要保留日期值属性后续做计算的话，去掉外层的TEXT函数即可

常见问题 #

如果转换后单元格显示########，直接拉宽对应列的宽度即可，属于列宽不足导致的显示异常。

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