[EXCEL]根据身份证计算生日和性别

发布于 2018-06-07  21 次阅读


=IF(C3="身份证",(IF(LEN(D3)=18,DATE(MID(D3,7,4),MID(D3,11,2),MID(D3,13,2)),IF(LEN(D3)=15,DATE(MID(D3,7,2),MID(D3,9,2),MID(D3,11,2)),"错误身份证号"))),"请输入生日")
=IF(LEN(D3)=15,IF(OR(RIGHT(D3,1)="0",RIGHT(D3,1)="2",RIGHT(D3,1)="4",RIGHT(D3,1)="6",RIGHT(D3,1)="8"),"女","男"),IF(LEN(D3)=18,IF(OR(MID(D3,17,1)="0",MID(D3,17,1)="2",MID(D3,17,1)="4",MID(D3,17,1)="6",MID(D3,17,1)="8"),"女","男"),""))