首页 EXCEL-身份证号转年龄

EXCEL-身份证号转年龄

举报
开通vip

EXCEL-身份证号转年龄PAGEPAGE1EXCEL-身份证号转年龄第一篇:EXCEL-身份证号转年龄【身份证信息提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)*1=IF(A2<>“",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)显示格式均为yyyy-m-d。(最简单的公式,把单元格设置...

EXCEL-身份证号转年龄
PAGEPAGE1EXCEL-身份证号转年龄第一篇:EXCEL-身份证号转年龄【身份证信息提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)*1=IF(A2<>“",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)显示格式均为yyyy-m-d。(最简单的公式,把单元格设置为日期格式)=IF(LEN(A2)=15,”19“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2),MID(A2,7,4)&”-“&MID(A2,11,2)&”-“&MID(A2,13,2))显示格式为yyyy-mm-dd。(如果要求为“1995/03/29”格式的话,将”-”换成”/”即可)=IF(D4=”“,”“,IF(LEN(D4)=15,TEXT((”19“&MID(D4,7,6)),”0000年00月00日“),IF(LEN(D4)=18,TEXT(MID(D4,7,8),”0000年00月00日“))))显示格式为yyyy年mm月dd日。(如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd)=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),”19“&MID(A1:A2,7,6))显示格式为yyyymmdd。=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&”年“&MID(A1,11,2)&”月“&MID(A1,13,2)&”日“=IF(A1<>”“,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),”男“,”女“)(最简单公式)=IF(MOD(RIGHT(LEFT(A1,17)),2),”男“,”女“)=IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,”男“,”女“)从身份证号码中进行年龄判断=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)=DATEDIF(A1,TODAY(),“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY())-IF(LEN(A1)=15,”19“&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-VALUE(MID(B1,7,4))&”岁“=YEAR(TODAY())-IF(MID(B1,18,1)=”“,CONCATENATE(”19“,MID(B1,7,2)),MID(B1,7,4))按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“),TODAY(),”y“)以20XX年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)*1,”20XX-10-31“,”y“)按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)男性16周岁以下为男性16周岁(含16周岁)以上至50周岁为男性50周岁(含50周岁)以上至60周岁为男性60周岁(含60周岁)以上为女性16周岁以下为女性16周岁(含16周岁)以上至45周岁为女性45周岁(含45周岁)以上至55周岁为女性55周岁(含55周岁)以上为=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),”y“),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&”/“&MID(K1,11,2)&”/“&MID(K1,13,2),TODAY(),”y“)>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))=DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),”Y“)。=YEAR(NOW())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4))其中A1为输入身份证号码的单元格第一个公式得出的是实际年龄,就是按月算,第二个公式得出的是按年算的年龄.在Explorer中身份证号码转换成年龄的公式:=IF(LEN(A1)=15,YEAR(NOW())-(19&MID(A1,7,2)*1),YEAR(NOW())-MID(A1,7,4))另,1、身份证自动读取出生年月日和年龄的公式1)、[以下公式自动计算年龄,其中A2表示身份证单无格]=IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),”“)例子:假如计算出来的值为25.9岁,则显示为25岁符合真实情况2)、[以下公式自动计算出生年月日,其中A2表示身份证单无格]=--TEXT(IF(LEN(A2)=18,MID(A2,7,8),”19“&MID(A2,7,6)),”#-00-00“)例子:19XX年X月X日=--TEXT(IF(LEN(A2)=18,MID(A2,7,6),”19“&MID(A2,7,4)),”#-00")例子:19XX年X月第二篇:excel中知道身份证号如何计算年龄excel中知道身份证号如何计算年龄身份证号码在A1,那么可以用下面的公式计算年龄:=DATEDIF(TEXT(MID(A1,7,INT(LEN(A1)/2-1)),“#-00-00”),TODAY(),“Y”)比如A1中身份证号码:***113B1中输入上面的公式,结果显示:28根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,“女”,“男”),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,“女”,“男”),“身份证错”))或=IF(MOD(MID(A1,17,1),2),“男”,“女”)2、根据身份证号码求出生年月:=IF(LEN(B2)=15,CONCATENATE(“19”,MID(B2,7,2),“.”,MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),“.”,MID(B2,11,2)),“身份证错”))或=TEXT(MID(C7,7,8),“0000-00-00”)3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),“身份证错”))第三篇:从身份证号中提取性别和年龄Q:A1单元格中是15位的身份证号码,要在B1中显示性别(这里忽略15位和18位身份证号码的判别)B1=if(mod(right(A1,1),2)>0,“male”,“female”)请问这个公式有无问题,我试过没发现问题。但在某个网站看到作者所用的是如下公式:B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2),“female”,“male”)A:leaf道理都是一样的,不过你的公式比那个公式优质提取性别(无论是15位还是18位)=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1),2)=1,“男”,“女”),IF(MOD(MID(A1,17,1),2)=1,“男”,“女”如果身份证号的输入已是15或18位,用公式=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1)),2),“男”,“女”如何从身份证号码中提取出生日期、年龄和性别有许多时候,我们需要批量从身份证号码中提取村民的出生日期、年龄,有时也要标明其性别。其实这些信息在每人特定的身份证号码中都有体现,如众所周知的,身份证第7-14位标明的是此人的出生年月日,18位的倒数第2位或15位的倒数第1位是性别识别码,因此根据这一原理,通过使用excel的函数关系就可以分别提取出来。1、先来看看如何提取出生年月日例如某人身份证号码是***886,我们可以通过以下公式进行提取:在excel 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 中需要放置出生年月日数据的单元格内输入“=MID(F2,7,4)&“-”&MID(F2,11,2)&“-”&MID(F2,13,2)”(不含引号,其中蓝色突出标识的为你所引用数据,即身份证号码所在的单元格,下同)回车即可提取到1980-02-14格式的出生年月日了,如果需要1980年2月14日格式的,可以用下面的公式“=MID(F2,7,4)&“年”&MID(F2,11,2)&“月”&MID(F2,13,2)&“日””(不含引号)2、提取性别信息可以在相应的单元格内输入“=IF(MOD(RIGHT(LEFT(F2,17)),2),“男”,“女”)”,以上面的身份证号码为例,得到结果应该是“女”。3、提取年龄虽然我们无法直接从身份证号码中看出具体年龄,但可以通过稍加转换即可实现。可以在相应的单元格内输入“=20XX-MID(F2,7,4)”,以上面的身份证号码为例,得到结果应该是“27”。注:其中20XX为统计当年年份第四篇:利用身份证号自动提取出生日期和年龄性别在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。第一,身份证号码的组成。当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。ABCDE姓名身份证号码出生日期性别年龄张三***7861972年05月03日女第二,根据身份证号码自动提取出生年月在目标单元格输入公式:=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&“日”,回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。第三,根据身份证号码自动提取性别的方法在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),“男”,“女”)提示:IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。第四,根据身份证号码自动提取年龄的方法在目标单元格输入公式:=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),“#-00-00”),NOW(),“y”),回车确定。第五篇:Excel从身份证号中提取出生年月和年龄如何利用Excel从身份证号中提取出生年月和年龄等信息?20XX年12月16日07:41P.M.一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。例如,某员工的身份证号码(15位)是***,那么表示1972年8月7日出生,性别为女。如果能想 办法 鲁班奖评选办法下载鲁班奖评选办法下载鲁班奖评选办法下载企业年金办法下载企业年金办法下载 从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。二、提取个人信息这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列。图1输入身份证号码1.提取出生年月信息由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“720XX这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中:LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。如果需要显示为“70年12月”这样的格式,请使用DATE格式,并在“单元格格式→日期”中进行设置。2.提取性别信息由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错。例如性别信息统一在B列填写,可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)”,其中:LEN(C2)=15:检查身份证号码的长度是否是15位。MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。回车确认后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。如图2所示,现在这份报表无论是提取信息或是核对,都方便多了!查看文章Excel表中身份证号码提取出生年月,性别,年龄的使用技巧20XX年12月24日星期一12:171.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在A1单元格,在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&“-”&MID(A1,9,2)&“-”&MID(A1,11,2),MID(A1,7,4)&“-”&MID(A1,11,2)&“-”&MID(A1,13,2))回车确认即可。如果只要“年-月”格式,公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&“-”&MID(A1,9,2),MID(A1,7,4)&“-”&MID(A1,11,2))3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:说明:公式中的B2是身份证号根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,“女”,“男”),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,“女”,“男”),“身份证错”))根据身份证号码求年龄:=IF(LEN(B2)=15,20XX-VALUE(MID(B2,7,2)),if(LEN(B2)=18,20XX-VALUE(MID(B2,7,4)),“身份证错”))4.Excel表中用YearMonthDay函数取相应的年月日数据;另一方法:这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,“女”,“男”),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,“女”,“男”),“身份证错”))2、根据身份证号码求出生年月:=IF(LEN(B2)=15,CONCATENATE(“19”,MID(B2,7,2),“.”,MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),“.”,MID(B2,11,2)),“身份证错”))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),“身份证错”))提取身份证号码的每一位,类推:第一位=IF(LEN(C2)=15,MID(C2,1,1),MID(C2,1,1))第二位=IF(LEN(C2)=15,MID(C2,2,1),MID(C2,2,1))第三位=IF(LEN(C2)=15,MID(C2,3,1),MID(C2,3,1))Excel身份证号码提取的出生年月日格式为:1969年12月24日=“19”&MID(C2,9,2)&“年”&MID(C2,11,2)&“月”&MID(C2,13,2)&“日”格式为:196912=“19”&MID(C2,9,2)&“"&MID(C2,11,2)格式为:1969.12=”19“&MID(C2,9,2)&”.“&MID(C2,11,2)将光标移至需要输入日期的单元格中,然后输入“=”19“&MID(身份证单元格,9,2)&”年“&MID(身份证单元格,11,2)&”月“&MID(身份证单元格,13,2)&”日“”内容。其中MID(B3,9,2)就是从身份证号码的第9位开始取2位数,得出该员工的出生年份,MID(B3,11,2)就是得出该员工的出生月份,而MID(B3,13,2)则是该员工的出生日期,这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。EXCEL根据身份证号码提取性别、出生年月、求年龄EXCEL根据身份证号码自动提取性别和出生年月及求年龄注:1.身份证号码前6位是北京市北京县的号码,后面的数字是随输入的,如有雷同,纯属巧合,敬请见谅。2.所有求出的信息都是直接从身份证号码里取得的。3.退休日期按男60女55周岁计算4.有效性验证已直接放到A列的数据有效里,单独列出是为了方便查看5.年龄按周岁计算。公式,直接粘在单元格上,假如A列是身份证号码,B列是以下任意一个(A2行为身份证号码,B2为下列任一,类推):出生日期=IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)性别=IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)年龄=IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)全部改为18位=IF(LEN(A2)=15,REPLACE(A2,7,19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(A2,7,19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)全部改为15位=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))退休日期=IF(A2<>”“,EDATE(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),660+MOD(RIGHT(LEFT(A2,17)),2)*60),)号码有效性验证=OR(LEN(A2)=15,IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUM(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2)))所属省份=IF(A2<>”“,VLOOKUP(LEFT(A2,2),data,2,),)所属地区=IF(A2<>”",VLOOKUP(LEFT(A2,6),data,2,))
本文档为【EXCEL-身份证号转年龄】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
下载需要: 免费 已有0 人下载
最新资料
资料动态
专题动态
个人认证用户
is_041319
暂无简介~
格式:doc
大小:23KB
软件:Word
页数:19
分类:
上传时间:2023-01-22
浏览量:21