Excel从身份证号中提取出生年月和年龄
用Excel从身份证号码中提取信息(年龄、性别、出生地)
用Excel从身份证号码中提取信息（年龄、性别、出生地）1.出生年月日信息提取：方法一：在记录列中输入公式：=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00")，往下复制，无论15位还是18位身份证号码全部搞定，方法最简单。
方法二：在记录列中输入公式：=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00"))，往下复制，无论15位还是18位身份证号码全部搞定，公式增加了几个字符，原理差不多，结果一致。
原理：使用函数text、if、mid、len。
注意：1、B列存放身份证号码。
存放在其它列，则在公式中作相应调整。
2、计算出错（#V ALUE!），说明身份证号码有错。
3、日期显示格式，可在单元格格式中设置。
性别信息提取：在记录列中输入公式：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。
原理：使用函数IF、LEN、MOD、LEFT、RIGHT。
注意：1、B列存放身份证号码。
存放在其它列，则在公式中作相应调整。
2、计算出错（#V ALUE!），说明身份证号码有错。
出生地信息提取：在记录列中输入公式：=LEFT(B2,6)，往下复制，然后根据代码用VLOOKUP查询发证地或者是出生地信息。
Excel文件模板：从身份证号码中提取信息使用的模板：使用Excel从身份证号码提取信息.xls点击该图标，打开该EXCEL文件，另存为××文件，即可使用。
谢谢你的使用。
Excel从身份证号提取生日，这三种方法推荐给你
Excel从⾝份证号提取⽣⽇，这三种⽅法推荐给你
营长说
总有HR伙伴咨询，如何从⾝份证号中提取⽣⽇，不仅仅是提取8位⽇期数字，⽽是直接获取yyyy-mm-dd的⽇期格式。
今天营长将常⽤的三种⽅法推荐给你！
我国⾝份证号码是18位，其中前2位代表省/⾃治区/直辖市，3,4位代表市，5,6位代表县区，7-14位代表出⽣年⽉⽇，15-18位分别为顺序码和校验码。
所以需要把⾝份证中的7-14位数字提取出来，并变成⽇期格式。
01
数据分列
Excel分列在数据处理和规范化中经常⽤到的技巧，按钮位置如下图。
选中数据后，要⽤到【固定宽度
固定宽度】，操作演⽰如下：
需要注意以下⼏点：
确认不导⼊的数据
设置⽇期格式YMD
设置导出位置
02
快速填充
在Excel2013及以上版本的Excel中，使⽤快速填充功能可以从规则的数据中提取⼀定样式的数据。
快速填充需要给Excel软件做1个或多个例⼦，让电脑找规律。
快速填充的快捷键是【Ctrl+E】，效率更⾼。
注意事项：
预先设置成两位⽉份和⽇期格式：yyyy/mm/dd
⾄少做两个⽰例
03
函数公式
这⾥综合要⽤到MID和TEXT函数。
MID函数从第7位开始，提取8位长度⽂本。
TEXT函数转换显⽰为⽇期样式#-00-00
转换后仍然是⽂本，并不是真正的⽇期
--俗称减负，将其转换为数字，通过单元格格式显⽰为标准⽇期公式下拉即可完成员⼯信息的提取。
以上就是从⾝份证号中提取⽣⽇⽇期的3种常⽤⼩技巧，欢迎⼩伙伴们操作测试~。
在EXCEL中根据身份证号计算出生年月日、年龄
在EXCEL中根据身份证号计算年龄、出生年月、性别在EXCEL中如何根据身份证号计算年龄、出生年月、性别呢？下面来介绍一下，希望对您有帮助1.根据身份证号计算年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(L EN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)注意：A2单元格为身份证号，且为文本格式。
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,"男","女")计算出生年月日的公式=TEXT(TEXT(MID(D2,7,LEN(D2)/2-1),"#-00-00"),"e-m-d") D2为身份证。
利用Excel从身份证号中提取出生年月和年龄等信息
利用Excel从身份证号中提取出生年月和年龄等信息利用Excel从身份证号中提取出生年月和年龄等信息一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
图1 输入身份证号码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位数字。
电子表格中身份证号提取生日和年龄汇总
电子表格中身份证号提取生日和年龄汇总注：（以下公式假设身份证在A1列）1、身份证在excel里面输入的时候，18位的只能以文本方式输入或者前加‘单引号，如果是文本怎么提取？如果有’等其他符号如何提取？如果只提取年份yyyy 和只提取月日mm-dd怎么提取？a、如果是文本则输入此公式：=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00 -00")b、如果是前加’附号的，那就试试这个公式：=TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2),"00-00-00")c、如果只提取年份：=YEAR(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"))d、如果只提取月日：=TEXT(TEXT((LEN(A1)<=16)*19&MID(A1,7,6+(LEN(A1)>=18)*2) ,"00-00-00"),"mm-dd")补充：如果身份证输入时采用了特殊格式如510182000000000000自动填入，则将上述第二个公式改一下：=TEXT((LEN(A1)<=16)*19&MID(A1,3,6+(LEN(A1)>=18)*2),"00-00-00")-----------------------------------------------------2、假设你的生日是此格式*年*月*日，要修改为如下格式，则：a、如需修改为**.**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"))b、如需修改为**.**.**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),".",TEXT(A1,"mm"),".",TEXT (A1,"dd"))c、如需修改为**-**-**的表达式如下：=CONCATENATE(TEXT(A1,"yyyy"),"-",TEXT(A1,"mm"),"-",TEXT (A1,"dd"))------------------------------------------------------3、身份证号或日期提取年龄a、直接从身份证提取当年的年龄：=2012-MID(A1,7,4)b、假设你的日期格式为1970-3-27，则输入如下公式转化为你当天的年龄：=INT（（TODAY()-A1)/365）。
Excel表中身份证号码提取出生年月
Excel表中身份证号码提取出生年月,性别,年龄的使用技巧1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。
MI D()——从指定位置开始提取指定个数的字符（从左向右）。
对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析，可以通过下述操作，假如身份证号数据在A1单元格，在B1单元格中编辑公式，完成形如1978-12-24样式的出生年月日自动提取：=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)=1 8,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2012-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2012-VALUE(MID (B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;另一方法：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式。
excel从身份证号码提取出生年月和年龄
方法一：1.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(A 1,11,2))3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考：说明：公式中的B2是身份证号根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B 2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE (MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;方法二：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：说明：公式中的B2是身份证号1、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=1 8,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(B 2)=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)),"身份证错"))用Excel函数计算年龄几法函数原理:TODAY函数函数名称：TODAY主要功能：给出系统日期。
Excel中根据身份证号求年龄性别和出生日期
Excel中根据身份证号求年龄性别和出生日期在工作当中有很多表格涉及到个人信息，比如年龄、性别、出生日期等。
如果我们能根据身份证号自动生成其年龄、性别、出生日期等信息，那样会提高我们的工作效率，大大缩短我们的工作时间。
本文将教你根据身份证号利用公式求出相应个人的年龄、性别、出生日期。
1、身份证号的输入如果是在默认单元格格式情况下输入身份证号会发现表格里显示为类似“1.10225E+17”的科学计数情况。
要解决这种情况，可以采取以下两种方法：一是切换输入到英文状态，输入前加个’（一定要是英文输入法下的单引号）,再输入数字；二是选中表格中要输入身份证号的列，鼠标右击——“设置单元格格式”——“数字”选择“文本”。
2、求性别身份证号的第17位表示性别信息。
单数为男，双数为女。
这样只要判断第17位数的单双即可。
可以在性别的单元格（C2）下输入=IF(MOD(MID(B2,17,1),2),"男","女")。
其中“B2”为身份证所在的单元格。
可以根据自己需要设置为相应的单元格即可。
MID(B2,17,1)表示从B2单元格中字符串的第17位开始提取1位数字，本例中表示提取身份证号码的第17位数字。
MOD(MID(B2,17,1),2)用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。
IF(MOD(MID(B2,17,1),2),"男","女")则表示如果除以2以后的余数是1（为真），那么性格单元格（C2）显示为“男”，否则显示为“女”。
回车确认后，即可在C2单元格显示正确的性别信息。
3、求年龄这个比较简单，网上有很多公式，但都很复杂，我们的思路就是用当前年份减去出生年份即可。
在Excel中获取当前年份的函数是=year(now())。
所以我们的公式为：=YEAR(NOW())-MID(B2,7,4)4、求出生日期如果说我们规定要把出生日期按照类似“1985-01-28”的形式输出，则用公式=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)，其中，MID(B2,7,4)表示从B2单元格中字符串的第7位开始提取4位数字，即出生年。
Excel中从身份证号码中提取出生年月、性别及年龄
Excel中从身份证号码中提取出生年月、性别及年龄Excel中从身份证号码中提取出生年月、性别及年龄一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
1.根据身份证号自动出生年月日MID函数的作用是返回文本字符串中从指定位置开始的特定数目的字符（该数目由用户指定）。
语法：MID(text,start_num,num_chars)Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一个字符的位置。
文本中第一个字符的start_num 为1，以此类推。
Num_chars 指定希望MID 从文本中返回字符的个数。
函数为：MID(A1,7,8)，显示的时候格式为YYYYMMDD。
如果想把出生年月日的格式写成YYYY-MM-DD的格式，那么函数可以写成如下格式：=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)2. 提取性别信息函数1：=IF((MOD(MID(A1,17,1),2)=0),"女","男")MID(A1,17,1)------从单元格A1中的第17位数字开始提取1位数字。
MOD(MID(A1,17,1),2)=0-----把提取的数字取余。
IF((MOD(MID(A1,17,1),2)=0),"女","男")-----若余数为0，则性别为女，否则为男。
办公教程：Excel中如根据身份证号码自动提取出生日期
Excel中如根据身份证号码自动提取出生日期
工作中我们可以从系统中导入身份证号码信息，Excel中可以很快根据身份证号码得到人员出生年月，今天我们就一起学习方法。
方法/步骤
第一步，打开待编辑数据表格。
第二步，选中出生日期单元格，找到【公式】——【插入函数】。
第三步，插入函数窗口中输入MID，找到此函数。
第四步，在弹出的【函数参数】窗口中输入参数。
字符串就是身份证数据单元格，开始位置7（因为身份证中第7位开始是出生日期），字符个数8（出生日期合计8位数）。
第五步，鼠标放在单元格右下角，当变成+号时，拖拉鼠标复制公式，所有数据全部生成。
第六步，选中所有出生日期数据列，鼠标单击右键，选择【复制】。
第七步，在出生日期列上右键【粘贴为数值】。
（公式提取后需要转化为数值，才可以进行下一步）
第八步，选中出生日期列，利用【数据】——【分列】把数据设置成日期格式。
（具体步骤可以翻看小编之前的教程《Excel中如何将八位数字设置为日期格式》）
第九步，设置完成后所有数据就会转化成日期格式。
总结，根据身份证号码提取出生日期，主要使用到：函数MID实现的。
注意事项
教程中身份证号码是作者编撰的，如有相似、相同，纯属巧合。
Excel表格中根据身份证号码自动填出生日期、计算年龄
Excel表格中根据身份证号码自动填出生日期、计算年龄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),"#-0 0-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)))WPS表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。
而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。
幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。
所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。
身份证号放在A2单元格以下的区域。
我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。
由于现在使用的身份证有15位和18位两种。
所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。
excel小技巧--通用身份证号码提取年龄,出生日期,性别
龄、性别
性别
女
0
男
1
男
1
男
1
男
1
女
0
女
0
女
0
男
1
女
0
男
1
女
0
注：此演示讲解版本 为office2016
从身份证中提取员工的性别
1、由于员工身份证号码中第十七位数值如果为奇数时，这位 员工为男性。如果身份证号码中第十七位数值为偶数时，这位员工 则是女性。 ２、为此，需要把身份证号码中的第十七位数值提取出来。 既函数式子为 =IF(MOD(MID(C3,17,1),2)=0,"女","男") =mid(C3,17,1) 这一函数式子的含义是从对应"张三"身份证 号码空格"C3"中提取第十七位的数值一个。 ＝mod(mid(C3,17,1),2) 含义为从"mid"函数式中提取出来 的数值通过除以２来确认是奇数，还是偶数。 =IF(MOD(MID(C3,17,1),2)=0,"女","男") 含义为如果提取 出来的数值等于0时，是偶数，是女性；不等于０时，是奇数，是 男性。 注：函数式中的逗号都必须选择在英语输入状态下输入。
从身份证中提取员工的年龄
EXCEL根据身份证号码自动提取性别、出生年月日和年龄（通用公式）
EXCEL根据身份证号码自动提取性别、出生年月日和年龄（通用公式）EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)这是根据身份证号码（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)),"身份证错"))其他通用公式：（新旧身份证号通用）（1）、根据“身份证号”获得“年龄”A1为身份证号，B1中输入公式=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),"#-00-00"), TODAY(),"y")（2）、根据出生“年-月-日”获得“年龄”B1为出生日期，C1中输入公式=INT((TODAY()-B1)/365)或者=DATEDIF(B1,TODAY(),"Y")判断身份证格式是否正确：B1为身份证号，C1中输入公式=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MON TH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1* (MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1 * (MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+19 00,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1* (MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1* (MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")。
excel根据身份证号码自动提取出生年月、性别、年龄的方法实例
excel根据身份证号码自动提取出生年月、性别、年龄的方法实例第一，身份证号码的组成。
当今的身份证号码由18位数字组成：前6位为地址码，第7至14位为出生日期码，第15至17位为顺序码，第18位为校验码。
15~17位为顺序号，其中第17位如果是单数为男性分配码，双数为女性分配码。
即通过第17位数字，可以判断性别，奇数为男，偶数为女。
下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。
首先，在excel中输入以下表格，实例如下面所示。
（身份证号码的输入方法见IT部落窝《excel如何输入身份证号码》文章介绍，网址是: /article/266.html）A B C D E姓名身份证号码出生日期性别年龄张三510321************ 1972年05月03日女37第二，根据身份证号码自动提取出生年月在目标单元格输入公式：=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表格中输入身份证号码自动识别性别提取出生年月计算年龄前言：相信很多做过文职的小伙伴有过相同的烦恼，特别在一些流动性很大的公司，每当有人员流动，都要重新录入员工基本信息，比如身份证号码-性别-出生日期，那有没有什么好方法，只要输入身份证号码，就能自动把性别和出生年月和年龄自动提取出来呢？当然有，在exc el表格中就能实现了。
工具：excel表格（office各种版本与WPS都适用）excel自带函数IF，MOD，MID，LEN，YEAR，NOW（每个函数的作用这里我就不讲了，自己百度）教程：1.1新建如图所示的身份证-性别-出生年月-年龄格式的表格，（因为这些是基本信息，所以我们制作员工信息表格的时候可以将这些基本信息放在一起，然后后面在添加一些其他的，比如入职日期、工龄等等其他一些杂七杂八的）1.2在性别下面的第一个单元格也就是D4单元格输入=IF(LEN(C4)=1 8,IF(MOD(MID(C4,17,1),2)=1,"男","女"),"")（这里为了信息保密我用的假信息做演示）细心的小伙伴可能发现了，这里就用到了4个函数了，IF、LEN、MOD、MID。
其实在exc el表格中用的最多的其实就是IF函数了，用来判断，比如制作成绩表的时候，只需要一个IF函数就行了。
1.3下面我们输入身份证。
（因为第二代身份证开始都是18位的了，这里我就用18位的作为演示，15位的基本都快消失了，所以可以忽略了。
）当身份证位数输入不正确时，性别会显示为空。
不过也有小伙伴说，自己做的表格说身份证输入错误时，显示的是#V ALUE。
其实也没问题啦，网上的教程是判断条件是C4>0的时候，但是因为数值不满足计算，导致输出数值错误，就显示这个了，不过也没错啦。
输入18位正确的号码时就能正确显示了。
那我们为了美观，可以用我的方法，这样就不会显示#V ALUE啦。
怎么在excel表中提取出生年月及年龄
怎么在excel表中提取出⽣年⽉及年龄
怎样在excel表中提取出⽣年⽉及年龄
我们在⼯作中经常会出现⼀些⾝份证号码，填表时要显⽰出年龄，那么⾃⼰⼝算效率明显没有电脑算⾼，今天我就教⼤家怎么在Excel中提取年⽉⽇及年龄。
例：
A1是⼤家熟悉⾝份证号码，将⽇期提取在B1表中，“=MID(A1,7,8)”其中MID是提取的意思，7代表从A1的第7位开始提取，8代表提取8位。
=TEXT(MID(A1,7,8),"#-00-00") 公式⾥边的Text是⽂本的意思，后边的”#-00-00”是格式。
结果就变成1988-06-03。
=DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),TODAY(),"y") 公式中DATE、TODAY分别代表⽇期和今天，其中”Y”、”M”、,’’D”,分别代表年、⽉、⽇。
如果想计算出从出⽣到现在共计多少天，可以将”Y”改成”D’。
用这4个公式，2分钟提取1000个身份证号中的生日，年龄，性别
⽤这4个公式，2分钟提取1000个⾝份证号中的⽣⽇，年龄，性别⾝份证号虽然只有短短的18位，但是“暴露”的信息却不少。
⽐如：你的出⽣地、⽣⽇、年龄、星座、⽣肖、性别……有些信息可以从⾝份证号中⼀眼看出，有些则需要计算和推算，当遇到⼤量信息需要从⾝份证号中提取时，这4个公式，可以提升我们的⼯作效率。
❦提取⽣⽇❶在D2单元格中，输⼊公式=TEXT(MID(C2,7,8),'0-00-00')❷将⿏标放置在D2单元格下⽅，待箭头变成⼗字形，下拉填充数据。
tips：MID（从哪提取，从第⼏个开始，要提取⼏个）⽤TEXT函数，将⽂本字符串转化为⽇期格式。
❦提取年龄❶提取虚岁年龄：在E2单元格中，输⼊公式=YEAR(TODAY())-MID(C2,7,4)。
❷提取实岁年龄在F2单元格中，输⼊公式=DATEDIF(TEXT(MID(C2,7,8),'#-00-00'),TODAY(),'Y') tips：DATEDIF（开始时间，结束时间，返回类型）。
❦提取性别在G2单元格中，输⼊公式=IF(MOD(MID(C2,17,1),2),'男','⼥')tips：⾝份证号的17位，奇数代表性别男，偶数代表性别⼥MOD为求余函数，MID(C2,17,1)为被除数，2为除数IF为判定条件返回不同值，当结果为1时，显⽰男，当结果为0时，显⽰⼥。
点击左上⽅☝☝，关注我~持续分享更多简单易学、功能强⼤的Excel技巧，让你轻松⼜⾼效的掌握Excel办公技能。
如果你想实战速成Excel技巧，可以浏览我的新专栏【7天Excel实战速成】。
Excel表格身份证号码提取出生日期地公式
Excel表格身份证号码提取出生日期的公式（B2表示身份证号码所在的列位置）=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2. 15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
3. 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。
一、提取出生年月：A、15位身份证号码：=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。
B、18位身份证号码：=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。
二、提取性别：18位身份证号码：=IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。
*excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思？IF是选择函数，当MOD(MID(E4,17,1),2)=0成立时，单元格显示“女”，否则显示“男”。
MOD是取模函数，即是一个求余函数，求MID(E4,17,1)除以2的余数。
实质是判断MID(E4,17,1)的奇偶性。
MID从一个文本字符串的指定位置开始，截取指定数目的字符。
MID(E4,17,1)是从E4单元格的文本中的第17个字符开始，取一个字符。
三、提取年龄：=year(today())-value(right(left(B2，10),4)) 回车确认即可。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
假设数据在A1单元格B1单元格输入公式=right(a1,len(a1)-2) (公式中的“2”是指栓出前面的位数）或=substitute(a2,"SF",)双击填充柄填充或下拉填充然后复制B列,选择性粘贴为数值,替换到A列中如何利用Excel从身份证号中提取出生年月和年龄等信息？2007年12月16日 07:41 P.M.一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
图1 输入身份证号码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单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。
如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！查看文章Excel表中身份证号码提取出生年月,性别,年龄的使用技巧2007年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,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,200 7-VALUE(MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;另一方法：这是根据身份证号码（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(LE N(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(S UM(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(M ID(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,))。