在EXCEL表格中如何用函数把身份证号码里面的出生年月日单列出来，如何计算年龄？

Question

如题所述

Answer 1

假设证号在B2
C2输入计算生年月公式(15,18位证号通用)
=IF(B2="","",DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,(LEN(B2)=18)*2+9,2),MID(B2,(LEN(B2)=18)*2+11,2)))

D2输入计算年龄公式
=DATEDIF(C2,TODAY(),"Y"))
或
=IF(LEN(B2)=15,YEAR(NOW())-1900-VALUE(MID(B2,7,2)),IF(LEN(B2)=18,YEAR(NOW())-VALUE(MID(B2,7,4)),"身份证错"))

E2输入性别
=IF(B2="","",IF(MOD(MID(B2,(LEN(B2)=18)*2+15,1),2),"男","女"))
或
=IF(B2="","",IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,17,1)),2)=0,"女","男"),"身份证错")))