我相信很多小伙伴都遇到过各种各样的“假”日期，在使用这样的数据时可能会给我们带来一些不便。这里所说的“假”日期是指看起来像日期，其实不是真正的日期。通常情况下，我们需要将“假”日期转换成“真”日期，这样才更有利于我们应用各种函数公式、进行运算、分组、应用筛选、在高级筛选中添加条件等。所以，今天我们就来说说“假”日期和“真”日期的问题。
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--
联系客服
