求excel身份证号中提取出年龄,出生年月日,性别,判断最简公式

I4为身份证号码
H4为出生日期
G4为年龄
F4为性别
以下公式同时适用于15位和18位身份证号码

F4=IF(LEN(I4)=18,IF(ISBLANK(I4),"",IF(MID(I4,15,3)/2=INT(MID(I4,15,3)/2),"女","男")),IF(ISBLANK(I4),"",IF(MID(I4,13,3)/2=INT(MID(I4,13,3)/2),"女","男")))

G4=IF(ISBLANK(I4),"",DATEDIF(CONCATENATE(MID(H4,1,4),"-",MID(H4,6,2),"-",MID(H4,9,2)),TODAY(),"y"))

H4=IF(LEN(I4)=18,IF(ISBLANK(I4),"",CONCATENATE(MID(I4,7,4),"年",MID(I4,11,2),"月",MID(I4,13,2),"日")),IF(ISBLANK(I4),"",CONCATENATE("19",MID(I4,7,2),"年",MID(I4,9,2),"月",MID(I4,11,2),"日")))

如果你的身份证号在A1
在B1输入公式

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y")

B1中就会是实际年龄

B2中输入公式

=IF(MOD(VALUE(MID(A1,17,1)),2)=1,"男","女")