身份证号码提出生年月性别年龄方法公式

第一篇:身份证号码提出生年月性别年龄方法公式

       一、提取出生日期

       如果我们要从一个人的身份证号码中批量提取其出生年月日,并表示成“yyyy-mm-dd”形式,可以这样做,假设身份证号码在C列,在D列中输入公式=IF(LEN(C6)=15,“19”&MID(C6,7,2)&“-”&MID(C6,9,2)&“-”&MID(C6,11,2),MID(C6,7,4)&“-”&MID(C6,11,2)&“-”&MID(C6,13,2))这个公式的含义就是,当其检查到C6单元格中的数据是15位的时,就显示“19”&MID(C6,7,2)&“-”&MID(C6,9,2)&“-”&MID(C6,11,2)的计算结果,否则就显示MID(C6,7,4)&“-”&MID(C6,11,2)&“-”&MID(C6,13,2)的计算结果。

       如:若C6单元格中是***,在D6单元格中计算出的结果是“1950-01-12”;若C6单元格中是***794,在D6单元格中计算出的结果是“1945-11-16”。

       二、提取性别

       在E6单元格输入公式=IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),“女”,“男”),IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),“女”,“男”))

       这个公式的含义是如果C6单元格是一个15位数,就显示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),“女”,“男”)的计算结果;否则,显示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),“女”,“男”))的计算结果。

       最后把D6和E6单元格的公式向下拉下来,在C6列输入身份证号码后,出生日期和性别可自动输入了,这样就减少了用户输入数据工作量,提高了办事效率!

       三、计算年龄

       excel中通过15位或18位身份证算年龄,精确到年

       假设C6为身份证号码,保证身份证号码输入正确情况下,使用如下公式: 15位用如下函数:

       =(TODAY()-DATE(MID(C6,7,2),MID(C6,9,2),MID(C6,11,2)))/365 18位使用如何函数:

       =(TODAY()-DATE(MID(C6,7,4),MID(C6,11,2),MID(C6,13,2)))/365 如果既含有15位又含有18位可以用如下综合运用。即增加增加判断函数,可使用如下函数:

       =(TODAY()-IF(LEN(C6)=18,DATE(MID(C6,7,4),MID(C6,11,2),MID(C6,13,2)),DATE(MID(C6,7,2),MID(C6,9,2),MID(C6,11,2))))/365

       上述函数是根据天数算出来的年龄,如果根据年的话可用如下函数

       =DATEDIF(TEXT(((LEN(C6)=15)*19)&MID(C6,7,INT(LEN(C6)/2-1)),“#-00-00”),“2022-12-31”,“Y”)

       四、根据出生年月计算年龄

       excel表格输入了出生年月怎么自动生成年龄,详细方法如下 输入的出生年月日必须为19840501或1984.5.1这两种格式 设日期在A2单元格,那么在B2单元=DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),TODAY(),“y”)&“周岁”

       此函数计算,录入出生年月日到至今的岁数。

       格内输入

       2022/3/22 随手记

第二篇:通过身份证号码提取年龄 性别 出生年月

       通过身份证号码提取年龄 性别 出生年月(excel和金山电子表格通用)

       通过身份证号码提取年龄 性别 出生年月(excel和金山电子表格通用)

       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))

       ----------------------------

       这是根据身份证号码(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)),“身份证错”))

       接下来我们应将光标移至D3单元格中,然后输入“=IF(MID(B3,15,1)/2=TRUNC(MID(B3,15,1)/2),“女”,“男”)”。这就表示取身份证号码的第15位数,若能被2整除,这表明该员工为女性,否则为男性。

       从身份证号码中提取年龄

       IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),“身份证错”))

       在excel中使身份证号自动算出性别、年龄、出生年月2022-11-22 20:00

       一、假如身份证号码在“A列”,如A2中,1、判断年龄:如果想在Excel中“B列”单元格显示年龄,那么点击所在列的第一个待输入的单元格,在这个单元格编辑区内输入以下公式:

       =IF(A2<>“",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6 (LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)

       如果你的身份号码在D列的D5或F列。那么记得更换公式中的“A2”为“D5”,记得更换四处哟。

       最后托动柄,向下复制。

       2、判断性别:

       在C2中输入=IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)3生在出生年月日:A列为身份证号码,在B列显示出生年月日

       =IF(A3<>”“,TEXT((LEN(A3)=15)*19&MID(A3,7,6 (LEN(A3)=18)*2),”#-00-00“) 0,)二

       A2, 为一组15位, 或18位身份证码 1] B2, ”性别“公式为 : =IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)2]

       C2,”

       年

       龄

       “

       公

       式

       为

       : =DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6 (LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“)

       三、假如A1中输入的是身份证号,格式为文本格式。

       则在B1中输入=YEAR(NOW())-VALUE(MID(A1,7,4)),算出的是此人的周岁。

       在C1中输入=LOOKUP(MOD(VALUE(MID(A1,17,1)),2),{0,1},{”女“,”男"}),显示性别。

       注:对于18位身份证号

第三篇:EXCEL中如何从身份证号码求出生年月日及年龄公式

       EXCEL中如何从身份证号码求出生年月日及年龄公式,性别信息方法?

       注:

       今天帮助一位网友处理一些表格的问题,也使自己重新又温习了一下电子表格的相关内容,在解决这个问题的过程中,借鉴了一位网友的文章,感觉里面的信息非常全面,可能许多朋友都有可能遇到,特转载到自己的博客当中。也对这位网友表示感谢。

       excel中如何根据身份证号,自动求出出生年月?

       很好用!根据我的实际工作需要,又少作修改的方法: 假如身份证号数据在A1单元格,在B1单元格中编辑公式

       =IF(LEN(A1)=15,“19”&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2))这样输出格式就都是19821010这种格式了。

       excel中如何根据身份证号,自动求出性别?

       假定身份证号在A1单元格,则在B1单元格中编辑公式

       =IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),“男”, IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1,2)=1),“男”,“女”))

       在excel是根据身份证自动填充年龄?

       假设A列从A1开始是身份证号,在B列中进行转换,则在B1中输入 =YEAR(TODAY())-IF(LEN(A1)=15,“19”&MID(A1,7,2),MID(A1,7,4)),然后再向下复制即可。

       精确核算年龄

       =DATEDIF(TEXT(TEXT(MID(A1,7,6 (LEN(A1)=18)*2),“#-00-00”),“e-mm-dd”),TODAY(),“y”)注:A1为身份证号所在单元格

       公式解析:

       一、分析身份证号码

       其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是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.提取出生年月信息

       由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。在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单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。

       在EXCEL中根据身份证号码计算年龄、出生年月、性别

       1、根据身份证号计算年龄(周岁)

       =IF(A2<>“",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6 (LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)

       注意:A2单元格为身份证号,且为文本格式。

       虚岁

       =2022-RIGHT(LEFT(C3,10),4)

       2、根据身份证号计算出生年月日 第一种,计算出来的格式为 ××年××月××日

       =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)&”日“)

       第二种,计算出来的格式为 ××年××月

       =IF(LEN(A2)=15,”19“&MID(A2,7,2)&”年“&MID(A2,9,2)&”月“,MID(A2,7,4)&”“&MID(A2,11,2)&”月“)

       3、根据身份证号计算性别

       =IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,”男“,”女")

       年

第四篇:由身份证号码求出生日期、年龄、性别

       excel中由身份证号求出生日期、性别、年龄

       (1)根据身份证号码求性别 :=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)

       (2)根据身份证号码求出生日期:

       =CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)

       (3)根据身份证号码求出年龄:=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周岁”)

       或 =DATEDIF(D3,TODAY(),“y”)

       1、身份证号码相关知识

       在了解如何实现自动从身份证号码中提取出生年月、性别信息之前,首先需要了解身份证号码所代表的含义。我们知道,当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的,现在签发的身份证由于年份的扩展(由两位变为四位)和末尾加了效验码,就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下:

       (1)15位的身份证号码:1~6位为地区代码,7~8位为出生年份(2位),9~10位为出生月份,11~12位为出生日期,第13~15位为顺序号,并能够判断性别,奇数为男,偶数为女。

       (2)18位的身份证号码:1~6位为地区代码,7~10位为出生年份(4位),11~12位为出生月份,13~14位为出生日期,第15~17位为顺序号,并能够判断性别,奇数为男,偶数为女。18位为效验位。

       2、应用函数

       在此例中为了实现数据的自动提取,应用了如下几个Excel函数。(1)IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。

       语法形式为:IF(logical_test, value_if_true,value_if_false)(2)CONCATENATE:将若干个文字项合并至一个文字项中。语法形式为:CONCATENATE(text1,text2……)

       (3)MID:从文本字符串中指定的起始位置起,返回指定长度的字符。语法形式为:MID(text,start_num,num_chars)(4)TODAY:返回计算机系统内部的当前日期。语法形式为:TODAY()

       (5)DATEDIF:计算两个日期之间的天数、月数或年数。语法形式为:DATEDIF(start_date,end_date,unit)(6)VALUE:将代表数字的文字串转换成数字。语法形式为:VALUE(text)(7)RIGHT:根据所指定的字符数返回文本串中最后一个或多个字符。语法形式为:RIGHT(text,num_chars)(8)INT:返回实数舍入后的整数值。语法形式为:INT(number)

       4、公式写法及解释(以员工Andy为例说明)

       说明:为避免公式中过多的嵌套,这里的身份证号码限定为15位的。如果您看懂了公式的话,可以进行简单的修改即可适用于18位的身份证号码,甚至可适用于15、18两者并存的情况。

       (1)根据身份证号码求性别

       =IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)公式解释:a.RIGHT(E4,3)用于求出身份证号码中代表性别的数字,实际求得的为代表数字的字符串

       b.VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字 c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数,当然你也可以用Mod函数来做出判断。

       d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判断出这个号码是偶数时,显示“女”,否则,这个号码是奇数的话,则返回“男”。

       (2)根据身份证号码求出生日期

       =CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解释:a.MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串 b.MID(E4,9,2)为在身份证号码中获取表示月份的数字的字符串 c.MID(E4,11,2)为在身份证号码中获取表示日期的数字的字符串 d.CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))目的就是将多个字符串合并在一起显示。

       (3)根据参加工作时间求年资(即工龄)=CONCATENATE(DATEDIF(F4,TODAY(),“y”),“年”,DATEDIF(F4,TODAY(),“ym”),“个月”)公式解释:

       a.TODAY()用于求出系统当前的时间

       b.DATEDIF(F4,TODAY(),“y”)用于计算当前系统时间与参加工作时间相差的年份

       c.DATEDIF(F4,TODAY(),“ym”)用于计算当前系统时间与参加工作时间相差的月份,忽略日期中的日和年。

       d.=CONCATENATE(D

第五篇:关于身份证性别年龄查询公式(03版)

       关于身份证号码的检测

       1、身份证中有X的需大写。

       2、检测身份证正误的公式

       以身份证号在E列5行为例

       =IF(OR(LEN(E5)=15,IF(LEN(E5)=18,MID(“10X98765432”,MOD(SUM(MID(E5,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*2^(18-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17})),11) 1,1)=RIGHT(E5))),“身份证正确”,“身份证错误”)

       复制到最后一列,复制完成后必须用ctrl shift 回车(也可直接回车),这是数组函数,必这样,然后一直往下拉就行了

       3、查重复公式

       以身份证号在E列5行为例,结果显示:1证明没有重复。显示:2证明身份证号有错误,检索一下就能发现。

       =SUMPRODUCT(N(CLEAN($E$6:E$6000)=CLEAN(E6)))

       4、这些仅用来检查身份证的,至于姓名正误和个人其他信息正误,一定要人工认真检查。、15位或者18位身份证性别公式

       =IF(MOD((IF(LEN(E6)=18,MID(E6,17,1),MID(E6,15,1))),2)=0,“女”,“男”)

       6、15位或者18位身份证年龄公式

       =YEAR(TODAY())-(IF(LEN(E6)=18,MID(E6,7,4),“19”&MID(E6,7,2)))

       7、15位或者18位身份证出生年月公式

       =IF(LEN(H3)=18,MID(H3,7,4)&“-”&MID(H3,11,2),“19”&MID(H3,7,2)&“-”&MID(H3,9,2))