excel从身份证号码中提取出生日期、性别公式

1、第二代身份证号码提取出生年月日的公式：=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)注：A是列，1是行，&在数字键7的上方。

2、第二代身份证号码提取性别男、女的公式：=IF(MOD(MID(A1,17,1),2),"男","女")注：A是列，1是行。

3、excel用身份证号算年龄的工式比如身份证号保存在a1单元格，那么可以使用=mid(a1,7,4)来取出出生年，再用当前年份减去这个就是年龄了。

4、提取身份证出生年月="19"&MID(B1,9,2)&"年"&MID(B1,11,2)&"月"&MID(B1,13,2)&"日"5、提取身份证年龄=YEAR(NOW())-YEAR(IF(LEN(B1)=18,DATE(MID(B1,7,4),MID(B1,11,2),M ID(B1,13,2)),DATE(MID(B1,7,2),MID(B1,9,2),MID(B1,11,2))))6、提取身份证性别=IF(LEN(B1)=15,IF(MOD(MID(B1,15,1),2)=1,"男","女"),IF(MOD(MID(B1,17,1),2)=1,"男","女"))7、公式内的“B1”代表的是输入身份证号码的单元格。

Excel自动提取身份证中生日和性别以及年龄（注意：以B2单元格中是18位身份证号为例）一、提取出生日期的输入公式=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"二、判断性别“男女”的输入公式=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")三、利用身份证号码求年龄=IF(B2="","",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN (B2)=18)*2),"#-00-00"),TODAY(),"y"))八、计算年龄的公式？datedif(开始日期，结束日期，"M")九、用公式计算最大年龄？max(D3:D21)十、用公式计算25--35岁之间的人数？=COUNTIFS(D3:D21,">"&25,D3:D21,"<"&35)十一、计算25岁以下的人数?=COUNTIF(D3:D21,">"&25)十二、计算25岁以下人数的比例?=COUNTIF(D3:D21,">"&25)/count(D3:D21)十三、用excel做表输入年月日，可以变成例如1986-01-01的格式吗？能的话怎么变？先选中单元格，鼠标右击，“设置单元格格式”，“数字”，“自定义”，在类型处输入e-mm-dd 确定十四、1.自动录入性别:“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”if假如(len(C2)得到C2的字符长度等于15，[15时mod除以2的余数(取mid(C2单元格,第15个的，1个字符），除以2）]，[不等于15条件不成立时if(mod后面除以2的余数(取C2，第17个，的1个字符），除以2）]所涉及函数：len() 取单元格字符长度。

在Excel中自动提取身份证中生日和性别
1.转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式(单元格内显示的是科学记数法的格式)，需要更改一下数字格式。

选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。

2.“提取出”出生日期
=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"
/判断性别“男女”
=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")
求年龄问题,要看你的数据是哪种格式(日期、文本），举例说明：假定你的A1单元格为日期格式，内容为“1986-2-20”，在B1单元格可
输入如下公式得到年龄：
=INT((TODAY()-A1)/365)。

如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别本公式只适合于18位身份证号码即二代居民身份证。

假如：A1输入身份证号码、B1显示性别、C1显示出生日期、D1显示周岁。

（要注意A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格）需要设置：A1单元格为文本格式、B1和D1为常规格式、C1为日期格式。

（选中列右键设置单元格格式可选取格式）1、显示性别：B1输入公式：=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")))))2、C1输入公式：=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",VALUE(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#年00月00日"))))))3、D1输入公式：=IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(), "y")))注意：A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格如下图那么上述B1、C1、D1输入函数中应将A1替换成F4即可。

[转] Excel表格中根据身份证号码自动填出生日期、计算年龄、录入男女的函数公式编辑|删除|权限设置|更多▼更多▲设置置顶推荐日志转为私密日志转载自瑞军转载于
2010年07月27日22:27阅读
(0)评论
(0)
分类：
网络转摘权限:
公开
18位身份证号码转换成出生日期的函数公式：
如果E2中是身份证,在F2中求出出生日期，
F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))
自动录入男女：
=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男")15/18位都可以的公式：
转换出生日期：
=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEXT(MID(e2,7,6),"#-00-00")) 自动录入男女：
=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))
计算年龄（新旧身份证号都可以）：
=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2,9,2),107-
MIDB(E2,7,2)))60~80的随机涵数公式
楼主你好，随机公式为=INT(RAND()*(60-81)+81），其中随机产生值不包含81，最小60，最大80。

最新第⼆代⾝份证号码提取出⽣年⽉⽇的公式（很简单）1、第⼆代⾝份证号码提取出⽣年⽉⽇的公式：=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)注：A是列，1是⾏，&在数字键7的上⽅。

2、第⼆代⾝份证号码提取性别男、⼥的公式：=IF(MOD(MID(A1,17,1),2),"男","⼥")注：A是列，1是⾏。

3、excel⽤⾝份证号算年龄的⼯式⽐如⾝份证号保存在a1单元格，那么可以使⽤=mid(a1,7,4)来取出出⽣年，再⽤当前年份减去这个就是年龄了。

4、提取⾝份证出⽣年⽉="19"&MID(B1,9,2)&"年"&MID(B1,11,2)&"⽉"&MID(B1,13,2)&"⽇"5、提取⾝份证年龄=YEAR(NOW())-YEAR(IF(LEN(B1)=18,DATE(MID(B1,7,4),MID(B1,11,2),MID(B1,13,2)),DATE(MID(B1,7,2),MID(B1,9,2),MID(B1,11,2))))6、提取⾝份证性别=IF(LEN(B1)=15,IF(MOD(MID(B1,15,1),2)=1,"男","⼥"),IF(MOD(MID(B1,17,1),2)=1,"男","⼥"))7、公式内的“B1”代表的是输⼊⾝份证号码的单元格。

Excel⾃动提取⾝份证中⽣⽇和性别以及年龄（注意：以B2单元格中是18位⾝份证号为例）⼀、提取出⽣⽇期的输⼊公式=MID(B2,7,4)&"年"&MID(B2,11,2)&"⽉"&MID(B2,13,2)&"⽇"⼆、判断性别“男⼥”的输⼊公式=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"⼥","男")三、利⽤⾝份证号码求年龄=IF(B2="","",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN (B2)=18)*2),"#-00-00"),TODAY(),"y"))⼋、计算年龄的公式？datedif(开始⽇期，结束⽇期，"M")九、⽤公式计算最⼤年龄？max(D3:D21)⼗、⽤公式计算25--35岁之间的⼈数？=COUNTIFS(D3:D21,">"&25,D3:D21,"<"&35)⼗⼀、计算25岁以下的⼈数?=COUNTIF(D3:D21,">"&25)⼗⼆、计算25岁以下⼈数的⽐例?=COUNTIF(D3:D21,">"&25)/count(D3:D21)⼗三、⽤excel做表输⼊年⽉⽇，可以变成例如1986-01-01的格式吗？能的话怎么变？先选中单元格，⿏标右击，“设置单元格格式”，“数字”，“⾃定义”，在类型处输⼊e-mm-dd 确定⼗四、1.⾃动录⼊性别:“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","⼥"),IF(MOD(MID(C2,17,1),2)=1,"男","⼥"))”if假如(len(C2)得到C2的字符长度等于15，[15时mod除以2的余数(取mid(C2单元格,第15个的，1个字符），除以2）]，[不等于15条件不成⽴时if(mod后⾯除以2的余数(取C2，第17个，的1个字符），除以2）]所涉及函数：len() 取单元格字符长度。

Excel中用身份证号提取性别、生日、年龄的方法，干货分享
1.用ISEVEN函数提取性别。

原理：身份证号第17位如果偶数，性别女，如果奇数，性别男。

函数功能：ISEVEN函判断数字是否为偶数，如果是，返回TURE；如果不是，返回FALSE。

IF函数在公式中设置判断条件，通过判断条件是否成立返回逻辑值TRUE或FALSE，然后根据结果返回不同的值。

MID函数为文本提取函数，格式为MID（要提取的文本字符串，从第几位开始，提取的位数）。

具体函数使用如下：
提取性别
2.用DATE函数提取出生日期。

原理：身份证号7-10位为出生年，11、12位为出生月，13、14位为出生日。

函数功能：DATE函数用于返回年月日组成的日期序列数。

MID函数为文本提取函数，格式为MID（要提取的文本字符串，从第几位开始，提取的位数）。

具体函数使用如下：
提取生日
3.用公式计算截止今天的年龄。

（1）设置E列单元格格式为数值型，小数位数为1位。

设置单元格格式
（2）用公式“=(TODAY()-D2)/365”计算出截止今天的年龄，精确到小数位数1位。

计算年龄
以上位在Excel中用身份证号提取性别、生日以及根据出生日期计算年龄的方法，相信学会了一定会事半功倍。

在EXCEL中怎样用身份证号计算年龄假设身份证号码在A1单元格 B1单元格输入公式=datedif(text(mid(a1,7,len(a2)/2-1),"0-00-00"), now(),"y") 此公式适用于15位和18位号码假设身份证号在A1单元格都是18位的在B1单元格输入公式按月份=year(today())-year(--text(mid(A1,7,8),"0-00-00"))或按年=datedif(--text(mid(A1,7,8),"0-00-00"),today(),"Y")在Excel中自动推测出生年月日及性别的技巧大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对于老式的15位身份证而言，7-12位即个人的出生年月日，而最后一位奇数或偶数则分别表示男性或女性。

如某人的身份证号码为420400*********，它的7-12位为700101，这就表示该人是1970年元月1日出生的，身份证的最后一位为奇数1，这就表示该人为男性；对于新式的18位身份证而言，7-14位代表个人的出身年月日，而倒数第二位的奇数或偶数则分别表示男性或女性）。

根据身份证号码的这些排列规律，结合Excel的有关函数，我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的，减轻日常输入的工作量。

Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。

提示：MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)，其中Text是包含要提取字符的文本串；Start_num是文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为2……以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。

从身份证号码中自动生成性别和生日生成性别：（其中B2是身份证号码所在列）一性别双击性别所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),"")),2)+1,"女","男")2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))二出生日期提取出生日期：（其中B2是身份证号码所在列）双击出生日期所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))三计算年龄：（其中C3是出生日期所在列）双击年龄所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可=YEAR(NOW())-YEAR(C3)Excel自动从身份证中提取生日性别出处：天空软件作者：佚名日期：2009-09-16每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。

数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。

有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在Excel2003中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。