excel 根据身份证显示各种信息

Posted on Posted in excel

excel 根据身份证显示各种信息

第一列 身份证 A2

511600199708017612

第二列 性别 B2

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

第三列 出生日期 C2

=TEXT(MID(A2,7,8),"0-00-00")

第四列 年龄 D2

=DATEDIF(C2,TODAY(),"y")

第五列 生肖 E2

=CHOOSE(MOD(MID(A2,7,4)-2008,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

第六列 生日提醒 F2

=IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))-TODAY()>0,"生日未到",IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))-TODAY()=0,"今天生日","生日已过"))

第七列 还有多少天 G2

=DAYS(TEXT(C2,"mm-dd"),TODAY())

第八列 生日整合 H2

=IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))-TODAY()>0,DAYS(TEXT(C2,"mm-dd"),TODAY()),IF(DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))-TODAY()=0,"今天生日","生日已过"&DAYS(TEXT(C2,"mm-dd"),TODAY())*-1))
» 转载请注明来源:呢喃 » excel 根据身份证显示各种信息
相关文章

Leave a Reply

Your email address will not be published. Required fields are marked *

10 + nine =