1、身份证验证：
=IF(LEN(C3)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(C3,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(C3),IF(LEN(C3)=15,ISNUMBER(--TEXT(19&MID(C3,7,6),"#-00-00"))))
2、身份证位数转换：15位转18位
=IF(LEN(C2)=15,REPLACE(C2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(C2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),C2)
3、身份证位数转换：18位转15位
=IF(LEN(D2)=18,LEFT(REPLACE(D2,7,2,),15),D2)
4、性别提取
=IF(MOD(MID(D2,IF(LEN(D2)=15,15,17),1),2)=1,"男","女")
5、出生年月
=IF(D2<>"",TEXT((LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),"#-00-00")+0,)ppt简历模板下载免费下载
=IF(LEN(D13)=18,IF(ISBLANK(D13),"",CONCATENATE(MID(D13,7,4),"年",MID(D13,11,2),"月",MID(D13,13,2),"日")),IF(ISBLANK(D13),"",CONCATENATE("19",MID(D13,7,2),"年",MID(D13,9,2),"月",MID(D13,11,2),"日")))
6、年龄提取
=IF(MONTH(TODAY())<MONTH(F2),IF(YEAR(TODAY())-YEAR(F2)<2,"0岁"&MONTH(TODAY())+12-MONTH(F2)&"月",YEAR(TODAY())-YEAR(F2)-1&"岁"&MONTH(TODAY())+12-MONTH(F2)&"月"),YEAR(TODAY())-YEAR(F2)&"岁"&MONTH(TODAY())-MONTH(F2)&"月")
7、工龄提取
=IF(MONTH(TODAY())<MONTH(H2),IF(YEAR(TODAY())-YEAR(H2)<2,"0年"&MONTH(TODAY())+12-MONTH(H2)&"月",YEAR(TODAY())-YEAR(H2)-1&"年"&MONTH(TODAY())+12-MONTH(H2)&"月"),YEAR(TODAY())-YEAR(H2)&"年"&MONTH(TODAY())-MONTH(H2)&"月")
8、出生年：=IF(LEN(D2)=18,MID(D2,7,4),"19"&MID(D2,7,2))
  出生月：=IF(LEN(D2)=18,MID(D2,11,2),MID(D2,9,2))
  出生日：=IF(LEN(D2)=18,MID(D2,13,2),MID(D2,11,2))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。