常用技巧
- excel 用身份证识别生日
=IF(D16=”身份证”,IF(ISERROR(IF(LEN(E16)=18,DATE(MIDB(E16,7,4),MIDB(E16,11,2),MIDB(E16,13,2)),DATE(MIDB(E16,7,2),MIDB(E16,9,2),MIDB(E16,11,2)))),”“,IF(LEN(E16)=18,DATE(MIDB(E16,7,4),MIDB(E16,11,2),MIDB(E16,13,2)),DATE(MIDB(E16,7,2),MIDB(E16,9,2),MIDB(E16,11,2)))),”“) - 用身份证识别姓别
=IF(LEN(E16)=15,IF(OR(RIGHT(E16,1)=”0”,RIGHT(E16,1)=”2”,RIGHT(E16,1)=”4”,RIGHT(E16,1)=”6”,RIGHT(E16,1)=”8”),”女”,”男”),IF(LEN(E16)=18,IF(OR(MID(E16,17,1)=”0”,MID(E16,17,1)=”2”,MID(E16,17,1)=”4”,MID(E16,17,1)=”6”,MID(E16,17,1)=”8”),”女”,”男”),”“))