【Excel】数据清洗04|6大日期清洗场景,告别乱码、计算报错,考勤对账效率翻倍!
- 2026-04-21 18:08:13
做人事、财务、行政、销售的职场人，处理Excel数据时，最头疼的莫过于【日期清洗】。
从不同系统导出的日期，日期格式千奇百怪，各种问题层出不穷。
今天格子间为大家整理了：最常见的6大日期乱象，每个场景都有对应专属解决方法！
✨温馨提示（必看！）
①所有原始数据备份，避免数据丢失/出错，数据清洗通常会对原数做较大改动；
②删除无用的空格，可以用之前分享的【TRIM函数】率先清洗隐藏的空格。
场景1：纯数字/文本
适用场景：系统导出的日期是8位纯数字或纯文本（如20260316），Excel识别为数值或文本，无法进行日期计算或筛选。
方法1：数据分列法
①选中备份后的数字日期列，依次点击【数据-分列】；
②在弹框中选择【分隔符号-下一步】；
③然后不勾选任何分隔符，点击【下一步】；
④在列数据格式选择【日期-YMD-完成】（即年月日，也可以在下拉框中选择需要的格式）。
方法2：公式法
①输入公式：
=IFERROR(DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)),"格式错误")
②使用DATE函数后，显示的是日期代表的序列数，需要将格式改为日期格式；
③选中处理后的日期，【右击-设置单元格格式】，选择需要的日期格式。
场景2：带符号/文字的文本
适用场景：文本格式的日期，带点、斜杠、文字（如2026-03-16,2026/3/16,2026.3.16，2026年3月16日）。
方法1：数据分列法
参考场景1，依然适用。但分列结束后，带文字的日期，仍然显示汉字的年月日形式，若想显示也改变，需要重新设置单元格格式。
方法2：公式法
带符号或文字的文本日期，可以分成两种情况，分别是：
①带点的文本日期（2026.3.16）
=IFERROR(DATEVALUE(SUBSTITUTE(B2,".","-")),"格式错误")
②带【-】【/】【年月日】的文本日期（2026-03-16,2026/3/16,2026年3月16日）
=IFERROR(DATEVALUE(B2),"格式错误")
注意：公式处理后的日期，也可通过【右击-设置单元格格式】，选择日期格式。
场景3：英文文本
适用场景：外贸、跨境销售导出的日期，经常是英文缩写格式，不符合国内办公习惯。
公式法
①直接转换，写公式时就可以选定日期格式
=IFERROR(TEXT(A2,"YYYY-MM-DD"),"格式错误")
②使用公式后，通过设置单元格格式，选定日期格式
=IFERROR(DATEVALUE(B2),"格式错误")
场景4：将各种日期格式转为文本
可参考往期内容：【Excel】如何将不同的日期格式转换为文本？
本期新增：将英文日期转为文本
公式法：
=TEXT(B2,"dd-mmm")
=TEXT(B6,"dd-mmm-yy")
注意：
①公式输入后，需要将其复制粘贴为文本；
②理论上，使用TEXT函数可以将标准的日期格式，转换为任何你想要的文本形式，大家可以动手试试~
场景5：日期错位
适用场景：导出国外或特殊系统数据，如日期格式为【月-日-年】，分两种情况介绍。
情况1:原数据为日期格式，转换成习惯的日期格式
【右击-设置单元格格式】，设置成需要的显示格式即可。
情况2:原数据为文本格式，转换成习惯的日期格式
参考场景1，适用【数据分列】，但是日期下拉框需要选择【MDY】，保证和文本的年月日顺序一致。
场景6：带时分秒的日期
适用场景：考勤表、打卡记录导出的日期，经常会带多余的时分秒，下面依然分两种情况。
情况1:原数据为日期格式，去掉时分秒
①公式：
=IFERROR(INT(B2),"格式错误")
②公式处理后，【右击-设置单元格格式】，将单元格设置为日期格式。
情况2:原数据为文本格式，去掉时分秒
①依然可以使用上述公式：=IFERROR(INT(B2),"格式错误")
②也可以使用公式：=IFERROR(DATEVALUE(B4),"格式错误")
③公式处理后，【右击-设置单元格格式】，将单元格设置为日期格式。
避坑指南
1、转换为日期格式后，可以使用【筛选】功能进行检查，是否仍然存在“脏数据”；
2、单元格中若出现“####”，可以通过双击列宽或手动调整列宽，可能是单元格宽度不足；
3、公式法处理后，一定要【复制→粘贴为值】，避免后续移动单元格，公式出错；
📌系列追更：
本篇为Excel数据清洗 第5篇，下期讲解【空值批量处理】～
往期数据清洗内容：
【Excel】数据清洗02｜一键提取括号/逗号内指定内容，1秒搞定几百条数据！
【Excel】数据清洗03 | 快速删除多余空格，3种方法让你告别“空格焦虑”！
💬评论区说说：
你平时处理日期，最常遇到哪种乱码？还有其他“日期乱象”吗？欢迎评论区共享~
👇关注格子，学习更多办公实用技巧，2026争取少加班、早下班！
