Excel如何处理日期出错？

 在年底忙到昏天黑地的小 E，刚偷偷将自己从繁重的工作解放出来，打算刷个微博放松一下，就看到了 2019 年的放假安排——

本以为是个令人开心的消息，可是……为什么五一劳动节变成了一天假？一天就一天吧，正当我要开始幻想明年假期都去哪里玩时，领导路过了我的位置。

「小 E，上班不工作你干什么呢？上午交的汇总统计表里日期问题很多啊，我一修改完单元格格式，还显示得乱七八糟，下班前给我重新做一份！」

「好……」小 E 吓得一个激灵，立马坐正！欸，之前因为偷懒，简单汇总了几份统计表就上交了，结果我的同事们不注意规范日期格式，可是把我坑惨了……

为什么我的日期设置无法正确显示？

为什么我的公式显示的值是错的？

为什么我的日期在公式中找不到对象？

……

上面这些问题背后可能的原因其实都是——我们看到是假日期。

趁着 2019 年还没到来，小 E 今天就赶紧说说如何把假日期转化为真日期的方法，不把「假日期」的坑留到明年！

- 1 -

标准日期 YYYYMMDD 的转换大法

有时我们拿到的数据里，日期样式会是这样的：

标准的 YYYYMMDD 格式对吧？但很遗憾，这并不是 Excel 认可的日期格式。

那是不是设置单元格格式为「日期」就可以了呢？那我们来看一下设置为日期格式后的结果：

额，日期竟然全部成了一串#，而且拖动单元格列宽也拯救不了。

Excel 日期其实是一个有区间的数值，上限是 2958465。而这里的 20151012 已经超过了最大日期上限，所以日期转换失败。

所以到底该怎么办呢？

解决方法 1：TEXT 函数法

在 B2 输入公式如下，向下填充，然后设置 B 列单元格格式为日期。

=--TEXT(A2,"0-00-00")

嘻嘻，搞定！简单不？不过有些小伙伴肯定会有疑问，小 E 已经替大家准备好了答案——

Q： 为什么会在最前面加--符号？

A：所有用 TEXT 函数处理过的数据返回的都是文本型数据，加上--是为了将文本日期转换为标准日期，即数值。

Q：为什么上述公式操作完是一串以 4 或者 5 开头的数字？

A：Excel 里的日期本质是数字序列，1900 年 1 月 1 日开始起，每增加 1 天，数字序列就增加 1，到 2015 年也有 4 万多天了，所以一开始会显示成 4 开头的数字。这时不用着急，选中它们进行格式设置，将数字转化为日期格式即可。

解决方法 2：分列法

不喜欢用函数？有没有更简单的办法？其实还有一种更简单、更强大的做法——分列。

分列法能解决大多数的非标准日期问题，小 E 在这里强烈推荐这个办法哟！

- 2 -

国民日期 YYYY.MM.DD 的转换大法

下面这样的日期也是国人比较偏好的一种形式，but，Excel 是老外发明的，这种格式书写的日期并不被认可为日期格式，而被认为是文本字符串。

我们该怎么对这种日期进行格式转换呢？

解决方法 1：SUBSTITUTE 函数法

在 B2 输入如下公式，然后向下填充即可。

=--SUBSTITUTE(A2,".","/")

也就是说，我们只要想个办法，把国民日期替换成 Excel 认可的日期形式就可以了。

解决方法 2：替换法

这个函数太复杂，记不住，有没有更简单的方法？

当然！国民通用的吸心大法，不不不，替换大法也同样可以实现。

选中日期列，按下快捷键【CTRL+H】调出替换功能，在查找内容里输入.，在替换为里输入/，最后点击【全部替换】按钮，完成替换。

替换后，效果如下：

有些小伙伴又在这里遇到了问题，小 E 这就来解决一下。

Q：为什么我操作的结果有的是对的，有的是错的，出来的是这个样子呢？

A：列宽不够，将日期的列宽拉宽一点就好了，并不是出错了哦。

- 3 -

文本日期的转换大法

以上两种日期格式我们还可以分辨，但有时候，日期的水很深。比如下图，乍一看是标准日期，没毛病。但是事实真是如此吗？

我们用 VLOOKUP 函数来查询结果，却发现找不到对象，竟然是#N/A！

因为 E2 单元格是一个标准日期，A 列虽然表面看上去是标准日期，其实却是一个文本型日期，所以使用 VLOOKUP 进行查找时，找不到匹配的内容。

那么该如何转换这种日期呢？先告诉大家，改单元格格式为日期这种方法是没有效果的。文本型的数据通过改单元格格式是无法直接转换的。

不过小 E 这里有 2 个正确的解决姿势，这就告诉你——

解决方法 1：公式法

在 C2 输入公式如下，然后向下填充，最后设置单元格格式为日期即可。

=--A2

单元格格式解决不了的问题，公式可以解决~

解决方法 2：复制粘贴法

不想设置辅助列，纠结，不想用？没关系，其实简单的复制粘贴也能帮我们搞定这个问题！

操作步骤如下：

❶ 将日期列（A 列）设置为日期格式

❷ 复制日期列数据

❸ 粘贴到记事本

❹ 复制记事本中的数据

❺ 粘贴数据到 Excel 的日期