Excel中的日期总是不对？请先把日期规范这件事搞清楚

作者：office办公酱 ｜ 微信公众号：office办公酱

前言

做过月度报表的小伙伴，大概都遇到过这种情况：从系统里导出一份订单明细，日期列看着是正常的日期，比如2026/5/10，可一排序，5月的记录就跑到了10月后面；想用数据透视表按月汇总，月份那一栏里又是一长串具体的日期，怎么都没办法合并到月这一层。此时再拿去和另一张表按日期对数据，使用VLOOKUP经常会返回这样的结果#N/A。

其实这类问题并不是公式的问题，而是这些单元格里装的根本不是日期，只是长得像日期的文字。

本篇我们就来聊聊日期规范这件事：什么样的日期才算规范、不规范会带来哪些问题、我们如何将不规范的日期变成规范的？看完这一篇，排序、筛选、透视表分组、跨表匹配，这类问题就能迎刃而解了。

一、先搞清楚：Excel里的日期到底是什么

在正式开始之前，我们先来弄明白一件事：Excel眼里的日期，到底是个什么?

1.1 日期其实是一个数字

很多小伙伴会自然而然的认为，Excel认识2024年5月10日这几个字，其实它一个汉字都不认识。

我们可以把它理解成，Excel给每一天发了一个号码牌：

1900年1月1日是1号，往后每过一天加1。所以2024年5月10日，在它眼里就是45422。

你可以试试：选一个日期单元格，把格式改成常规，它立刻就会变回45422。

1、选中日期单元格，在开始选项开右侧将格式修改为常规。

2、查看结果。

其实日期之所以能相减、能排序、能按月分组，靠的都是这个数字身份。一旦它不是数字，这些功能就全失效了。

后面所有的问题，都是从这里开始的。

1.2 补充两个冷知识

顺便说两个冷知识，了解一下即可。

一是Excel为了兼容早期的Lotus 1-2-3，硬塞了一个并不存在的1900年2月29日（编号60）。所以计算1900年3月1日之前的日期，会差一天，不过我们日常基本涉及不到这种情况。

二是Mac上的老文件，可能开着1904日期系统，同一个日期，两套系统的编号相差1462天。如果你打开别人的文件，发现整张表日期差了好几年，可以先去查这个选项。

二、什么样的日期才算“规范”

那到底什么样的日期才算规范？我们可以从三个方面来看。

2.1 三条标准

第一，值是数值，不是文本。 这是基本要求。

第二，能被Excel认成日期。 此时才能算、能排、能筛。

第三，全表长得一样。 比如统一显示成yyyy-mm-dd格式。

这里要特别提醒一句：第一条和第三条，是两回事。

把一个文本日期改成“2024年5月10日”的显示格式，它只是看着像日期了，骨子里还是文本；反过来，真日期不管显示成什么样，值都不会变。

很多小伙伴就卡在这儿，以为格式刷一下就好了，结果刷完还是算不出来。

2.2 3秒判断法

那怎么快速判断？常用的有三个方法，三秒钟就能看出来。

方法一，看对齐。 Excel默认文本靠左，数值和日期靠右。

方法二，看状态栏。 框选一整列，如果底下只有计数，那基本全是文本；出现求和``平均值，才是真数值。

方法三，看公式。=ISTEXT(A2)返回TRUE就是文本，=ISNUMBER(A2)返回TRUE才是数值。更省事的办法是直接写=A2+1，能算出结果就是日期，报#VALUE!就是文本。

三、不规范的日期，常见的有这6种

3.1 6种常见写法

先来看一张示例表，这6种写法都是日常最常遇到的错误写法。

接下来我们要做的，就是把规范后的日期这一列做出来。

这里要说明一下：这些写法如果是一个字一个字敲进去的，Excel有时候会自作主张，帮我们转成日期；但只要是系统导出、或者从别处复制粘贴过来的，就多半直接是文本。

注意哦，第五种前面那个小撇号是看不见的，它的作用就是告诉Excel后面这串东西按文本处理。如果单元格被提前设成了文本格式，就算不敲撇号，输进去的日期也还是文本。

3.2 还有一类：显示不规范

前五种，本质是一类：长得像日期，但不是日期。第六种是另一类：它确实是日期，只是年份被理解错了。

还有一种情况要单独说：日期是真的，只是显示格式五花八门，有的是2024/5/10，有的是5月10日，有的是2024-05-10。这种不影响计算，只影响阅读，属于显示不规范，改格式就行，不用转换。

四、不规范会带来哪些问题

我们来看看日期不规范会带来哪些问题：

1、排序错乱。 文本是按字符比大小的。2024/10/1和2024/2/1放在一起，Excel比的是第一个不同的字符，10的1比2小，于是10月排到了2月前面。

2、筛选失灵。 日期筛选里的本月``介于``上季度，对文本日期统统不生效，它压根不认为那是日期。

**3、数据透视表分不了组。**真日期在透视表里右键就能组合→月/季度/年，文本日期只能一个个勾，几百条下来很费时间。

4、计算报错。 两列日期相减，直接#VALUE!；DATEDIF、EDATE、EOMONTH，一个都用不了。

5、匹配不上。 一张表是文本、一张表是数值，用VLOOKUP或XLOOKUP按日期查，必然是#N/A。很多人以为是公式写错了，其实问题出在数据上。

6、图表X轴乱序。 文本日期会被当成一个个独立分类，按录入顺序排，而不是按时间排。

五、怎么把不规范的日期转过来

原因搞清楚了，接下来我们开看看怎么把不规范的日期变成规范的，这里介绍两个方法，大家在使用时可以结合自身实际情况灵活运用。

5.1 方法一、分列

适用场景： 一整列都是不规范日期，写法比较规整（点、横线、斜杠分隔），想一次性批量转换。

这个方法比较省事，不用记公式，几分钟就能搞定。

操作步骤：

• • 1、为了保险起见，我们先复制一列数据做备份。因为分列会直接覆盖原数据，转错了就麻烦了。
• • 2、选中日期列，切到数据选项卡，点击分列。

• • 3、第一步选分隔符号，点击下一步。

• • 4、第二步什么都不用设置，直接下一步。

• • 5、第三步是关键：把列数据格式选为日期，右边的格式选YMD（年月日），点击完成。

• • 6、查看效果。

框选之后状态栏也出现了求和，那就说明它们已经变回真正的日期了。

注意事项

• • 分列会覆盖原列，动手前一定先备份；也可以在第三步指定目标区域，把结果放到旁边的空列。
• • 如果转完还是文本，说明这种写法分列没认出来，可以采用方法二。

5.2 方法二、函数（零散数据、特殊写法）

适用场景： 数据量不大、写法特殊（8位数字、带“年月日”），或者想留着原列做对照。

这个方法的核心只有一句话：不管它长什么样，最后都用DATE(年,月,日)把它拼出来。

这里解释一下：公式前面那两个减号，作用是把文本硬转成数值，可以简单理解为负负得正。DATE拼出来的本来就是数值，所以不用加。

如果结果显示为45422这样一串数字，不用担心，在开始选项卡里把单元格格式改成日期就好了。

注意事项

• • TEXT()转出来的是文本，好看但不能算。后面还要汇总、排序、算天数的，最好别用它。
• • 最后记得把结果复制，选择性粘贴为值，再把原列删掉。

六、从源头规范表格数据

转换是补救错误，其实最好的办法，是让它一开始就规范。下面这四步，建议我们直接养成习惯。

6.1 先把格式设成日期，再让人填

顺序一定不能反。在文本格式下录入的日期，永远是文本。先选中整列，把格式设成日期，再开始录。

6.2 用数据验证拦住错误输入

切到数据选项卡，打开数据验证，允许选日期，数据选介于，开始日期填2000/1/1，结束日期填2099/12/31。

再到输入信息里写清要求，比如“请按2024-05-10填写”；出错警告里写一句提示。

这样填错的时候，Excel会当场弹窗拦住，比事后一个个改方便很多。

6.3 统一显示格式

选中日期列，右键打开设置单元格格式，类别选自定义，类型里输入yyyy-mm-dd。

6.4 做成模板

把规范好的表另存为模板（.xltx），以后都从模板新建；要发给别人填的，再保护工作表、锁定格式列。

七、总结

日期这件事，其实并不复杂，简单总结就三步：先判断它是不是数字，不是就用分列或函数转回来，最后从录入源头卡住错误的日期格式。

如何选择正确的方法呢？可以参考下面的速查表，直接对号入座就好了:

很多小伙伴学了一堆日期函数，却总是算不出结果，有时候问题往往不在函数，而在数据本身。日期不规范的话，函数用得再熟练也无法发挥出它的作用。

这里是office办公酱，如果大家在工作里也遇到过类似的问题，可以在文章底部留言，我们一起讨论解决。觉得这篇文章对你有帮助的话，欢迎点赞、关注、转发给更多的人，我们下期再见！