欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  Office

Excel 根据身份证号计算年龄、提取性别、计算退休时间

程序员文章站 2022-03-04 19:21:16
前几天,咱们分享了一期根据身份证号计算出生年月的内容,今天再和大家分享一期有关身份证的计算: 一、根据身份证号计算年龄: =DATEDIF(TEXT(MID(A...

前几天,咱们分享了一期根据身份证号计算出生年月的内容,今天再和大家分享一期有关身份证的计算:

一、根据身份证号计算年龄:

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")

Excel 根据身份证号计算年龄、提取性别、计算退休时间

首先用MID函数,从身份证号码中提取出表示出生年月的八位数 19810727 ,再使用TEXT函数将其变成日期样式“1981-07-22”。

DATEDIF函数以此作为起始时间,以TODAY(当前日期)作为结束时间,计算两个日期之间的整年数。

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

二、根据身份证号码提取性别:

=IF(MOD(MID(A2,15,3),2),"男","女")

Excel 根据身份证号计算年龄、提取性别、计算退休时间

先用MID函数,从第15位开始,提取3个字符“562”。也可以使用MID(A2,17,1),即只提取第17位的数字。

接下来用MOD函数判断该数值与2相除的余数,结果是1或是0。

最后用IF函数判断,如果MOD函数结果是1,返回结果“男”,否则返回结果“女”。

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

三、根据身份证号码计算退休时间:

=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)

Excel 根据身份证号计算年龄、提取性别、计算退休时间

这个公式咋一看的话,会感觉比较绕,咱们一段一段的解读。

1、TEXT函数结合MID函数,用于提取出出生日期。

2、EDATE函数用于计算指定日期N个月之后的日期,这里指定的日期就是TEXT函数的计算结果。

3、最后是指定月份的计算。其中600是固定的,600个月也就是50年。MOD函数结合MID函数,计算出性别码的奇偶性,结果是1或是0,再用1或是0乘以120(10年),如果性别是男,则是1*120+600,结果是720(60年)。如果性别是女,则是0*120+600,结果是600(50年)。

4、EDATE函数以出生日期作为开始时间,根据性别的不同,分别计算出600个月(50年)或是720个月(60年)之后的日期,结果就是退休日期。

提示:这个公式马上就要废弃了,原因嘛,TMD,都懂的……

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

四、最后是咱们的练手题目

看看下面这个图,A列是身份证号码,但是这些身份证号码里有短横线进行间隔。

我们要做的就是去掉这些短横线,使其变成完整的身份证号:

Excel 根据身份证号计算年龄、提取性别、计算退休时间

快把你的方法告诉老祝吧!