在C列中输入身份证号,在D列中输入此公式,可算出此人的出生年月日,不管是18位的,还是15位的身份证。 =IF(LEN(C4)=15,CONCATENATE("19",MID(C4,7,2),"年",MID(C4,9,2),"月",MID(C4,11,2),"日"),CONCATENATE(MID(C4,7,4),"年",MID(C4,11,2),"月",MID(C4,13,2),"日")) 男女判断: =IF(LEN(C4)=15,IF(MOD(MID(C4,15,1),2)=1,"男","女"),IF(MOD(MID(C4,17,1),2)=1,"男","女")) 年龄判断: =IF(E4="","",DATEDIF(E4,TODAY(),"Y"))
男女判断也可精简一些 =IF(MOD(MID(A1,15,3),2),"男","女")
|