excel中高效率的日期数据整理方法分享

在日常工作中，处理数据时除了查找、求和等之外，我们还会经常与日期数据打交道。

其中我们经常使用的函数，有Date（合并日期）、Year（获取日期的年份）、Month（获取日期的月份）、Day（获取日期的天数）等等。

这不，最近朋友发来一个数据，是从系统里面导出来的。如下图：

该系统自动将日期与型号混在一起，现在需要将其中的日期提取出来。

提取规则是：

❶前三个字符代表的是月份数。

❷第5和第6个字符代表两位年份数。

❸第7和第8个字符代表两位天数。

提取结果如下图：

也就是将英文表示的【月-年-日】转换成纯数字的【年-月-日】形式的日期。

问题分析

我们平时处理的日期大部分是由纯数字组成的，可能很少接触到这类英文日期。

不过一旦遇到了，我们还是要想办法解决的，对不对？

比如前面提到的这个案例，我们可以使用下面的函数来提取出【年-月-日】形式的日期。=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

结果却是错误值！

为啥？接着往下看你就知道啦！

先来看下公式解析：

❶我们先用MID(A2,5,2)，提取出年份数

从该文本的第5个字符开始，截取2个字符，结果如下图：

❷再用LEFT(A2,3)，提取出月份数

从该文本的第1个字符开始，截取3个字符，结果如下图：

❸之后用MID(A2,7,2)，提取出天数

从该文本的第7个字符开始，截取2个字符：

❹最后用Date函数将【年月日】整合在一起。=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

看上去没有问题呀？为什么会出现错误值#VALUE呢？

其实，问题就出在月份是英文字母。而不是数字。

Date函数要求【年月日】三个参数都必须是数字。（不论是文本型数字，还是数值型数字）

思路有了，下面我们就来看看怎么解决吧！

解决方案方案A

将英文月份转变成数字，我们可以使用Match函数。

公式如下：=MATCH("Mar",{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)

公式解析：

MATCH(lookup_value,lookup_array,[match_type])

该函数有三个参数：

❶查找值。

第一参数：就是我们这里要查找的月份，比如："Mar"，

❷在哪个区域或者数组中查找。

第二参数：我们需要构造一个1月-12月内存数组：

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

用Match函数去查找第一参数在第二参数中的位置，如果在第三个，就返回3，正好也是3月份的意思。

❸精确或者是模糊匹配。

第三参数：输入0，表示精确查找。

最终我们将上面公式整合在一起，结果为：

公式如下：=DATE(MID(A2,5,2),MATCH(LEFT(A2,3),"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec",0),MID(A2,7,2))

公式的第二部分由Match函数组成。返回数字月份。

这样结果就出来了！

但是，小伙伴有没有发现年份是错的，Excel自动给我们加了19数字。

这个还不是我们想要的，所以我们在年份前面加上20即可。

公式如下：=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A2,7,2))

如果小伙伴们不想手动输入月份的常量数组，也可以用下面的公式：

公式如下：=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),TEXT(20&-ROW($1:$12),"mmm"),0),MID(A2,7,2))

其中：TEXT(20&-ROW($1:$12),"mmm")

构造一个英文月份的常量数组。这个公式的结果正好是上面的英文月份数组。

有兴趣的小伙伴们可以参考下。方案B

另外，除了上面常规的套路之外，还有一种非常非常简单的方法，就是下面这种方法。

在【设置单元格格式】==》【数字】==》【日期】中，查看内置英文日期的显示方法。

从图中可以看出：

英文日期的显示方式是：【日-月-年】

我们可以通过这种方法来组合成日期形式，然后再转换成目标日期。如下图：

【B2】公式如下：=MID(A2,7,2)&"-"&LEFT(A2,6)

公式解析：

❶先用MID(A2,7,2)提取出天数：12

❷再用LEFT(A2,6)提取出月和年：Mar-22

❸最后用&符号再连接一个日期符号「-」：12-Mar-22

日期样式已经组合成系统内置的日期形式了。

但是，现在的问题是如何转换成纯数字的目标日期呢？

其实方法很简单！

就是进行一次数学运算即可！如下图：

【B2】公式如下：=--(MID(A2,7,2)&"-"&LEFT(A2,6))

公式解析：

先使用一对括号()放在原公式的外面，再使用两个负号（--）将其转换为纯数字日期。

就这样一个简单的转换就实现了我们的需求了，还是挺简单的吧！

写在最后

今天，我们分享了如何将英文日期转换为符合中国人习惯的纯数字日期。

❶使用日期和文本截取函数的常规方法将【月-年-日】转换成【年-月-日】。

❷模拟套用系统内置样式进行转换。

可见使用第二种方法，不仅公式非常简单，而且也大大的提高表格运行效率。

希望小伙伴们在平时的工作中，可以多多留意下系统里面内置的各种设置，肯定能发现不少好用的功能！