我相信很多小伙伴都遇到过各种各样的“假”日期,在使用这样的数据时可能会给我们带来一些不便。这里所说的“假”日期是指看起来像日期,其实不是真正的日期。通常情况下,我们需要将“假”日期转换成“真”日期,这样才更有利于我们应用各种函数公式、进行运算、分组、应用筛选、在高级筛选中添加条件等。所以,今天我们就来说说“假”日期和“真”日期的问题。
1、更改单元格格式法
首先,我们要明白一点,日期、时间本质上就是数字。在Excel中,1900-1-1作为第一天,对应数字1,后面的日期对应的数字就依次增加。比如2017-9-11就等于42989。了解了这一点,那么我们就可以尝试将日期所在的单元格的格式更改为“数字”来看看日期是否会变成数字。
如下图所示,A2单元格是一个“假”日期,A3、A4单元格是“真”日期。B列等同于A列的内容(这里为了对比,所以两列都放在这),将B列单元格格式更改为“数字”后,B2单元格样式不变,B3、B4变成了数字。这样我们就可以判断出B2是“假”日期,B3、B4是“真”日期了。
2、公式法
这里使用函数IsNumber来判断目标内容是否是数字。如果结果是True,也就是目标内容是数字,那它就可以代表一个日期。
3、观察法
其实,除了上面这两个方法,我们还可以通过观察法来判断。如上图所示,如果没有特别地设置单元格对齐方式的话,数字、日期都是靠右对齐的,文本是靠左对齐的。所以,我们可以看出来A7单元格靠左对齐,明显地不是“真”日期。
在我们使用数据做分析时,“假”日期会造成一些不便,比如:
1、不能直接使用Year、Month、Day、Datedif等日期函数;
2、做数据透视表时,日期不能自动分组;
3、应用自动筛选时,日期不能自动分组;
4、应用高级筛选时,如果作为条件字段不能筛选出正确的数据;
5、排序时不能按照日期大小来排序;
6、有些情况下,不能直接做加减运算;
7、不能应用自定义格式来设置成其他格式的日期。
……
我们通过几个示例来看看怎样将“假”的变成“真”的。
1、文本格式的日期
下面示例中,A2单元格格式是常规,但是日期前面加了一个单引号,使之变成了文本格式;A3单元格格式是文本,输入日期2017-9-11后,并不能自动转换成“真”日期。这种情况下,我们只要负负运算就可以得到“真”日期了。比如公式:=--A2。其实就是通过简单的数学运算,不改变原值,将文本变成了数值参与运算,最终得到的结果也是数值。同样的道理,也可以用乘以1或者加0运算。
上面的方法是在另外一列使用公式来得到结果,其实我们可以在原始列上直接运用计算或者转换来将它变成“真”日期。
一种方法是使用“选择性粘贴”。
操作方法:复制一个空白单元格,选中A2:A3区域,点击右键菜单中的“选择性粘贴”,在弹出的对话框中“运算”方式选择“加”。如果复制的单元格值是1,这里“运算”就换成“乘”。
关于选择性粘贴的详细用法,请参考以下文章。
另一种方法是使用“分列”。
操作方法:选中A列,点击【数据】选项卡下面“数据工具”组中的“分列”,前两步都取默认值即可,在第3步中“列数据格式”选择“日期”,根据选中列的日期格式,在后面的下拉列表中选择对应的日期格式,点击“完成”即可。
关于“分列”的详细用法,请参考以下文章。
2、带空格的日期
这种日期常见于从各种系统导出的数据中。
对于这种情况,我们可以复制其中一个空格,然后用“查找和替换”功能将其替换成空,也就是删掉空格。然后再使用分列、选择性粘贴、数学运算等任何一种方法就可以将其转换成“真”日期了。注意,这里为什么要说“复制其中一个空格”?这是因为,我们自己在“查找和替换”对话框中输入的空格跟数据中日期前后的空格不一定相同。所以,直接复制原数据中的空格来“查找和替换”就不会有问题了。
如果用公式的话,可以用Trim去掉前后的空格,再用负负数学运算,就可以转换成“真”日期了。比如公式:=--TRIM(A2)。
3、纯数字形式的日期
如下图所示,日期都是纯数字,没有 - 或者 / 作为分隔符,其实它们都不是“真”日期。将它们所在的单元格格式设置为“日期”,第一个得到了########,第二个还是文本。
这种情况下,我们可以用Text函数来转换为日期样式,然后再转换成“真”日期。
如下图中的公式:=--TEXT(A6,'0-00-00')
这里有一点要注意,日期必须是YYYYMMDD格式,或者YYMMDD格式,即要求月和日都是两位数字来表示,否则Text函数就无法判断哪个数字属于“日”,哪个数字属于“月”了。
除了使用Text函数,我们也可以直接使用“分列”的方式将其转换成“真”日期,这里就不再赘述。
4、用小数点作为分隔符的日期
比如2017.9.11,这种日期格式来源于数据记录人员的输入习惯,有些系统导出数据可能也会有这种样式。简单点来讲,我们还是可以使用“分列”的方式来转换。如果使用公式的话,可以用Substitute将.替换为-,比如以下公式:
=--SUBSTITUTE(A2,'.','-')
5、年份在后的日期格式
如下图所示的日期9/11/2017,这是Excel无法直接识别的,输入单元格后其实它是文本形式。这种情况,我们还是可以使用“分列”的方式,“日期”格式选择“MDY”。有没有发现这个“分列”简直万能了?还可以用公式来将其转换成“真”日期,比如几个截取函数就能搞定。
=--(RIGHT(A2,4)&'/'&LEFT(A2,LEN(A2)-5))
6、含有未知字符的日期
这个案例是来自于一个小伙伴的求助,他用Javascript将数据写入Excel,没有声明日期变量为“日期”类型,导致写入到Excel后日期的前后和中间都插入了几个不可见字符。
示例“2017年9月6日”总共9个字符,用Len函数统计出的结果却是14个,这说明里面有不可见字符。
用=CODE(LEFT(A2,1))得到结果是数字63,而实际上数字2的Code是50,这说明数字2不是第一个字符。
所以,我们采用以下公式来将其转换成“真”日期。
=--SUBSTITUTE(A2,LEFT(A2,1),'')
这个公式的含义是,将Left截取到的第一个字符替换为空,也就是删掉不可见字符。最终,我们可以看到已经转换成了日期格式。
关于真假日期,今天就介绍到这。再遇到真真假假的日期,小伙伴们都学会怎么辨别了吗?
--End--
联系客服