[Excel]5招快速拯救杂乱无章的日期格式

2017-03-23 本文已影响129人效率火箭

从不同渠道、不同系统采集来的数据，往往可能会出现数据格式不匹配的情况，比如。。。

某天火箭君在做某张透视表时，怎么也无法对“日期”格式的列进行分组？&……%￥#@

经过了一番仔细查看后才发现，原来原始数据中的日期格式都被保存成了文本格式，因此无法直接分组。

那究竟该怎么整这些乱七八糟的日期格式呢？

假设这里有四种日期格式的错误。第二列使用ISTEXT()函数判断了第一列日期的格式。

前两种错误是日期格式的形式，但被强行保存成了文本。因而即便选择日期格式，单元格中的内容仍无法被识别为日期。

第三种则使用了非系统认可的日期形式，也被识别为了文本。

第四种则是一串8位数字，因为没有使用任何分割符号，因此被识别为了数字。

对原日期列进行简单公式运算，可以是+0，-0，*1，/1。

DATEVALUE函数是一个把文本转成日期的函数命令。

复制原数据，然后选择“选择性粘贴”中的“加”，文本便被强制转换成了数字。再通过更改单元格格式，便能将部分日期文本转化为日期格式。

利用分列功能，最大的特点就是能够一下子解决这四种错误形式。

具体操作如下：

选中要处理的数据，选择数据标签中的分列功能

在向导第一步中，选择“分隔符号”

在向导第二步中，不选择任何符号，这样就能对所有可能得符号进行识别

在向导第三步中，选择"日期”

针对8位数字的日期，可以利用公式命令=TEXT(B4,"0000\/00\/00")+0

TEXT函数本身便可以以一种特殊的数字格式将一个数转换为文本。在这个案例中，TEXT函数的第二个参数被设置为了“0000\/00\/00”，从而将原先的8位数字转换成了“0000/00/00”这样的形式。

5中方法可以分成三类：

前三种方法，操作非常简单，但仅适用于日期形式的文本；

第四种方法最为万能，几乎所有有关日期形式的错误都可以通过分列进行处理；

而专门针对8位数字这样的日期表达，第五种方法提供了利用公式解决的额外方法。