excel身份证信息提取公式
2023-05-16 本文已影响0人
络筱矶
- 根据身份证号判断性别
=IFERROR(IF(ISODD(MID(身份证号,17,1)),"男","女"),"") - 提取年龄
=IFERROR(DATEDIF(TEXT(MID(身份证号,7,8),"0-00-00"),TODAY(),"Y")+1,"") - 提取生日
=IFERROR(TEXT(MID(身份证号,7,8),"0-00-00"),"") - 根据生日判断是否成年
=IFERROR(IF(DATEDIF(生日,TODAY(),"Y")>=18,"已成年","未成年"),"") - 根据性别、年龄判断是否适婚
=IFERROR(IF(性别="男",IF(年龄-1>=22,"适婚","不适婚"),IF(年龄-1>=20,"适婚","不适婚")),"") - 根据性别、生日提取退休时间
=IFERROR(IF(性别="男",YEAR(生日)+60&RIGHT(生日,6),YEAR(生日)+55&RIGHT(生日,6)),"") - 根据身份证号提取生肖
=IFERROR(MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(身份证号,7,4),12)+1,1),"") - 根据身份证号提取星座
=IFERROR(IF(身份证号="","",LOOKUP(--TEXT(MID(身份证号,11,4),"0-00"),--{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座")),"") - 生日提醒
=IFERROR("距离生日还有"&365-VALUE(DATEDIF(生日,TODAY(),"YD"))&"天","")
