徐州市审计局

巧用excel将身份证号码转换得到出生日期及性别

【发布日期】：2011-09-20 【来源】：本站原创 【关键字】：巧用excel将身份证号码转换得到出生日期及性别 【浏览量】: 503

　　早期的身份证号码为15位数字，现在使用的身份证号码为18位数字，它们的含义如下：

　　（1）15位：1-6位为地区代码，7-8位为出生年份（2位），9-10位为出生月份，11-12位为出生日期，第13-15位为顺序号，这3位奇数为男，偶数为女。

　　（2）18位：1-6位为地区代码，7-10位为出生年份（4位），11-12位为出生月份，13-14位为出生日期，第15-17位为顺序号，同上，第18位为效验位。

　　在审核参保人员是否符合领取补助的年龄时，需要通过依据身份证号码，自动提取出出生日期、性别、年龄等信息。
　　在excel中巧用以下几个公式，就可以轻松得到这些信息。

    注：A列中输入的是身份证号码。

　　1、出生日期

　　=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),"/",MID(A1,9,2),"/",MID(A1,11,2)),CONCATENATE(MID(A1,7,4),"/",MID(A1,11,2),"/",MID(A1,13,2)))

　　2、性别

　　=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<>1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))>TODAY()),"错误",IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")))))

　　3、至审核时候实际年龄

　　=IF(ISERROR(1*(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00")))<>1905-01-01"),(1*(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00")))>TODAY()),"错误",DATEDIF(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00"),TODAY(),"y")))

睢宁县审计局 王强