PC下载网
PC下载网 > 软件教程 > 办公应用 > 

Excel如何解决日期出错?

Excel如何解决日期出错?

作者:秋叶PPT 来源:PC下载网资讯 原创 时间:2018-12-21 21:58:46

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

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

u=3664447048,375956366&fm=26&gp=0.jpg

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

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

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

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

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

……

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

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

- 1 -

标准日期 YYYYMMDD 的转换大法

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

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

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

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

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

所以到底该怎么办呢?

微信图片_20181221220230.jpg

解决方法 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 是老外发明的,这种格式书写的日期并不被认可为日期格式,而被认为是文本字符串。

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

微信图片_20181221220241.jpg

解决方法 1:SUBSTITUTE 函数法

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

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

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

解决方法 2:替换法

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

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

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

替换后,效果如下:

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

Q:为什么我操作的结果有的是对的,有的是错的,出来的是这个样子呢?

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

- 3 -

文本日期的转换大法

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

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

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

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

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

微信图片_20181221220250.jpg

解决方法 1:公式法

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

=--A2

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

解决方法 2:复制粘贴法

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

操作步骤如下:

❶ 将日期列(A 列)设置为日期格式

❷ 复制日期列数据

❸ 粘贴到记事本

❹ 复制记事本中的数据

❺ 粘贴数据到 Excel 的日期

PC下载站网友:
共有12条评论
返回顶部