Excel之一在Excel中根据身份证号码利用公式自动生成出生年月日

如何在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】利用函数MID在身份证号码中提取出生日期
第一步：在单元格中填写“=”，然后点击“ƒx”插入函数，选择类别“文本”，找到函数“MID”，点击确定。

第二步：在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“7”，第三行填入“4”，点击确定。

第三步：在后面填入“&”年”&”，然后再次插入函数“MID”。

在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“11”，第三行填入“2”，点击确定。

（注意：双引号必须使用英文状态下的””）
第四步：在后面填入“&”月”&”，然后再次插入函数“MID”。

在函数参数中第一行选中身份证号码所在单元格“C2”，第二行填入“13”，第三行填入“2”，点击确定。

（注意：双引号必须使用英文状态下的””）
第五步：在后面填入“&”日””，点击Enter键，然后就得到了张三的出生日期。

（注意：双引号必须使用英文状态下的””）
第六步：选中第一个得到的数据，将鼠标放到表格右下角，让鼠标呈实心十字，然后下拉填充格式，得到所有数据。

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),"#-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)))WPS表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。

而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。

幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。

所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

身份证号放在A2单元格以下的区域。

我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。

由于现在使用的身份证有15位和18位两种。

所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。

在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为身份证。

编辑日期：2009年7月28日EXCEL表中身份证号码信息提取公式1、从身份证号码提取“年”份如：342622************提取1994，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

2、从身份证号码提取“月”份如：342622************提取11，输入公式：=IF(LEN(H2)=18,IF(MID(H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)),IF(MID( H2,9,1)="0",MID(H2,10,1),MID(H2,9,2)))，回车确定。

3、从身份证号码提取“日”如：342622************提取8（不是08形式），输入公式：=IF(LEN(H2)=18,IF(MID(H2,13,1)="0",MID(H2,14,1),MID(H2,13,2)),IF(MID( H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)))，回车确定。

4、从身份证号码提取“年月日”如：342622************提取形如1994-11-08，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2)&"-"&MID(H2,9,2)&"-"&MID(H2,11,2),MID( H2,7,4)&"-"&MID(H2,11,2)&"-"&MID(H2,13,2))，回车确定。

5、从身份证号码提取“年龄”如：342622************提取年龄为15岁（针对2009年），输入公式：=YEAR(NOW())-IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

用Excel从身份证号码中提取信息（年龄、性别、出生地）出生年月日信息提取：方法一：在记录列中输入公式：=--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批量提取出来？
有了身份证号，如何快速批量把出生日期提取出来。

下面介绍两种方法：
第一种方法：用函数将身份证号中的出生日期提取出来。

单击B2单元格，在单元格中输入公式：=IF(LEN(A2)=15,DATE(MID(A2,7,2),MID(A2,9,2),MID(A2,11,2)),IF( LEN(A2)=18,DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),'号码有错'))。

说明：如果身份证号确保正确也可以用这个公式：=TEXT(IF(LEN(A2)=15,19,'')&MID(A2,7,6+IF(LEN(A2)=18,2,0)),'#-00-00')。

如果身份证都是18位，可以用这个公式：
=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))
注意：一定要将存放日期的单元格格式设置成日期类型，具体设置如下：
选中存放日期的单元格，单击右键，在弹出的菜单中选择设置单元格格式：
然后在弹出的对话框中选择数字标签，选择日期类型，单击确定。

拖动填充柄，复制公式到所有需要提出日期的单元格中，大功告成：
第二种方法：用数据菜单中的分裂命令。

将A列中的身份证号复制到B列，选中B列所有身份证号，选择数据菜单，单击分裂命令，打开分列向导：
选择固定宽度，单击下一步，在日期前后分别建立分列线，单击
下一步：
选择中间日期数据列，将列数据格式设置成日期型，单击完成，大功告成。

完成效果如下：
好累啊，给个大拇指好不好，(*^__^*) 嘻嘻……。

编辑日期：2009年7月28日EXCEL表中身份证号码信息提取公式1、从身份证号码提取“年”份如：342622************提取1994，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

2、从身份证号码提取“月”份如：342622************提取11，输入公式：=IF(LEN(H2)=18,IF(MID(H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)),IF(MID( H2,9,1)="0",MID(H2,10,1),MID(H2,9,2)))，回车确定。

3、从身份证号码提取“日”如：342622************提取8（不是08形式），输入公式：=IF(LEN(H2)=18,IF(MID(H2,13,1)="0",MID(H2,14,1),MID(H2,13,2)),IF(MID( H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)))，回车确定。

4、从身份证号码提取“年月日”如：342622************提取形如1994-11-08，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2)&"-"&MID(H2,9,2)&"-"&MID(H2,11,2),MID( H2,7,4)&"-"&MID(H2,11,2)&"-"&MID(H2,13,2))，回车确定。

5、从身份证号码提取“年龄”如：342622************提取年龄为15岁（针对2009年），输入公式：=YEAR(NOW())-IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

【Excel之一】在Excel中根据身份证号码利用公式自动生成出生年月日一、先给实例，不用搞懂原理假设：A1栏内有身份证号码，要在B1栏中自动生成年月日，那么就在B1栏中输入以下公式。

（由于A1是假定的，可以任意替换，所以在下面的公式中用红色醒目标出）【情况1】公式如下：=TEXT((LEN(A1)=15)*19&MID(F2,7,6+(LEN(A1)=18)*2),"#-00-00")显示的效果是：年年年年-月月-日日，如1984-04-28【情况2】公式如下：=IF(LEN(A1)=18,MID(A1,7,8),19&MID(A1,7,6) )显示的效果是：年年年年月月日日，如公式如下：=IF(LEN(A1)=18,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,11,2)&"日","无效身份证号码"))显示的效果如下：XXXX年XX月XX日，如1984年04月28日【情况4】公式如下：=IF(LEN(A1)=18,MID(A1,7,4)&"."&MID(A1,11,2),IF(LEN(A1)=15,MID (A1,7,2)&"."&MID(A1,9,2),"无效身份证号码"))显示的效果如下：年年年年.月月，如1984.04二、有兴趣就拓展一下根据以上四种情况，在不需要理解公式含义的情况下，就可以派生出其他几种不同的显示格式出来。