EXCEL从身份证号码中提取生日、性别怎么操作

学习的目的是为了能让我们的效率提升，看看下面的情况你遇到过吗？你是怎么解决的呢？

1、提取出生日期

从身份证号码里提取出生日期，计算年龄，判断性别。

如上图，根据身份证号码，提取出生日期，计算年龄，判断性别。

大家都知道身份证号码第7位到第14位表示出生年月日，所以用mid直接从第7位开始提取，提取8位。

=MID(A2,7,8)，结果为19900224，这样虽然是提取出来了，但不符合我们常见的格式，且在计算年龄时结果肯定会错。怎么办呢？

我们在提取的时候就把他用DATE函数规范好。date（年，月，日）

=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

这样出来的结果就是1990/02/24。

上面是一种办法，我的直观思路是提取出来后直接按照想要的格式表达

=TEXT(MID(A2,7,8),"0000-00-00")

这样比较精炼。

接下来计算年龄就简单了，前面章节也讲过

=DATEDIF(B2,TODAY(),"y")

身份证号码第17位表示性别，奇数表示男，偶数表示女，所以判断性别也就是判断这个数字的奇偶性。除以2，余数是1表示男，是0表示女。

=IF(MOD(MID(A2,17,1),2)=0,"女","男")

2、综合判断

下面图片是原始数据单，是从系统里导出来的流向，做了简化，意在说明问题。

第二幅图是朋友想做成的样子：

发货日期从第一副图中提取。

是否返佣的条件为：

1、本笔记录的发货日期距离今天大于等于2个月。

2、本笔记录的发货日期之后又有至少一次发货记录，即要压着一次发货的钱。

上面两个条件对应的客户名称，存货，规格都是一致的。

上面的意思直白点就是客户进了我们的货了，要给人家返佣金，怎么返？进货之后两个月返给你，而且你至少要保证这两个月还有进货，否则你只进了一笔，我把佣金返给你了，你来个退货，那我不傻眼了！

因为这个流向数据是系统里导出来的，所以日期格式带了具体时间，我试过调整格式，调整不过来，朋友原来的做法是直接用left提取前面7位。其实这样是不对的，为啥？图片中的日期都是7位，如是出现17-11-25肯定就会出错。

所以我们应该提取8位，但是提取八位的话遇到只有6位的，

比如17-3-4 上午 12：00，提取结果就是17-3-4 上，连上字也提取出来了，挺头疼的！

思路一：在日期与上午或者下午之间用函数substitute和rept函数插入空格，然后提取。

思路二：是用find函数找到上或者下在日期中的位置，然后提取位数就是刚的结果减1。

我的思路：上面两种思路写函数都比较复杂，干脆简单点，时间里不是上午就是下午，都包含午字，那我们直接找午的位置

在第一副图的B2中输入=FIND("午",流向单!B2)，就找到了午的位置。

然后提取

=LEFT(流向单!B2,FIND("午",流向单!B2)-2），这样日期就提取出来了

按照格式表达

=TEXT(LEFT(流向单!B2,FIND("午",流向单!B2)-2),"yyyy-mm-dd")

提取日期圆满成功。

接下来我们看看是否返佣该怎么弄？

第一个条件本笔发货日期据今天大于等于两个月，这个好办。

第二个条件本笔发货日期以后有发货记录，其实只需要判断日期列中是否有大于本笔日期的数据存在，存在就说明后面有发货，反之就是没发货。

公式：

=IF(AND(SUMPRODUCT(($C$2:$C$7=C2)*($D$2:$D$7=D2)*($E$2:$E$7=E2)*(($B$2:$B$7)>B2))>1,DATEDIF(B2,TODAY(),"m")>=2),"是","否")

红色部分为第一个条件，本笔日期距离今天是否大于等于2个月；蓝色部分为第二个条件，客户名称，存货，规格相同的情况下，发货日期列中大于本笔日期的个数如果大于1就说名后面有发货，反之就是没发货。

and表示两个条件同时满足。

思路虽然简单，但是里面的数组思维大家得仔细领会。

转自：米宏Office