根据【中华人民共和国国家标准 GB 11643-1999】中有关公民身份号码的规定,公民身份号码是特征组合码,由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
根据身份证号提取生日
对于18位数字的二代身份证号码,其中第7~14位为出生日期码,代表YYYYMMDD;而一代身份证第7~12位仅六位数字表示出生日期,代表19YYMMDD。
Excel公式:
=--TEXT(MID(A2,7,8),"0-00-00") 或者 =TEXT(RIGHT(19&MID(A2,7,IF(LEN(A2)=15,6,8)),8),"0-00-00")+0 或者 =TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0
计算年龄
=DATEDIF(TEXT(RIGHT(19&MID(A2,7,IF(LEN(A2)=15,6,8)),8),"0-00-00"),NOW(),"Y") =DATEDIF(TEXT(MID([@身份证号码],7,8),"0-00-00"),TODAY(),"Y")
计算退休年龄
=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)
按男性60岁(720个月)退休,女性50岁(600个月)退休。
推算生肖
中国人有12生肖,属什么可以推算出来。即用诞生年份除以12,再用除不尽的余数对照如下:
0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊
Excel公式:
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(A2),12)+1,1)
判断性别
根据身份证号码第17位数字可以判别出性别:男性为单数,女性为双数。
Excel公式:
=IF(MOD(MID(A2,17,1),2),"男","女") 或者 =IF(MOD(MID(A2,15,3),2),"男","女") 或者 =IF(-1^MID(A1,15,3)=1,"女","男") 或者 =IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")
身份证校验码
校验码(身份证最后一位)是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。计算方法为:
- 将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2;
- 将前17位数字和系数相乘的结果相加;
- 用加出来和除以11,看余数是多少?
- 余数只可能有0、1、2、3、4、5、6、7、8、9、10这十一个数字。其分别对应的最后一位身份证的号码(效验码)为:1、0、X、9、8、7、6、5、4、3、2。
Excel公式:
=OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:17")),1),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1)=RIGHT(A2)))
自动验证输入
数据有效性公式:
=AND(LEN(A2)=18,OR(ISNUMBER(-A2),RIGHT(A2)="X"))
扩展:TEXT函数特殊用法
提取“-”前面所有字符:如A1单元格值为“028-83541879”,则下面公式结果为“028”。
=TEXT(,"[$"&A1&"]")