在EXCEL中怎样用身份证号计算年龄,性别及出生年月、查重

在EXCEL中怎样用身份证号计算年龄

假设身份证号码在A1单元格B1单元格输入公式=datedif(text(mid(a1,7,len(a2)/2-1),"0-00-00"),no w(),"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从文本中返回字符的个数。假定某单位人员管理希望通过身份证号码自动得出员工的出生年月日及性别，现以此为例，将有关步骤向广大用户作一

简要介绍：1. 选中整个B列，然后执行“格式”菜单中的“单元格”命令，打开“单元格格式”窗口。 2. 选择“数字”选项卡。 3. 在“分类”栏中选择“文本”选项，然后单击“确定”按钮，关闭“单元格格式”窗口，将所有包含有身份证号码的单元格设置为文本格式。 4.将光标移至C3单元格中，然后输入“="19"&MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日"”内容。其中MID(B3,7,2)就是从身份证号码的第7位开始取2位数，得出该员工的出生年份，MID(B3,9,2)就是得出该员工的出生月份，而MID(B3,11,2)则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。5. 接下来我们应将光标移至

D3单元格中，然后输入

“=IF(MID(B3,15,1)/2=TRUNC(MID(B3,15,1)/2),"女","男")”。这就表示取身份证号码的第15位数，若能被2整除，这表明该员工为女性，否则为男性。6. 最后我们只需利用自动填充功能对其他各个员工的出生年月日、性别进行填充即可。上面都是以15位身份证为例进行介绍的，18位身份证的操作方法与此类似，广大用户若使用的是18位身份证，只需对有关函数的取值位置进行适当调整即可（如将

“="19"&MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日"”修改为“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”）。这样就实现了由Excel自动填充员工出生年月日、性别的功能，从而极大地减轻了用户录入数据时的工作量，

有兴趣的读者不妨一试。

出生年月

18位

=DATE(MID(a2,7,4),MID(a2,11,2),MID(a2,13,2))

15位

=DATE(MID(e13,7,2),MID(e13,9,2),MID(e13,11,2))

或：

=IF(LEN(TRIM(a2))=18,(DATE(MID(a2,7,4),MID(a2,11,2),MI D(a2,13,2))),(DATE(MID(a2,7,2),MID(a2,9,2),MID(a2,11,2)))) 同时，设置一下单元格格式为日期格式就正常了

性别

=if(mod(if(len(trim(a2))=15,mid(a2,15,1),mid(a2,17,1)),2)=1,"男","女")

年龄

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

A2为身份证,A3输入

=DATEDIF(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)) ,TODAY(),"y")

根据第一列的不同类型让第二列每个类型自动从1编号的办法

B2=countif($A$2:$A2,$A2)

Excel四舍五入、不四舍五入或取整的相关函数

①ROUND函数

1、四舍五入：ROUND(目标单元格，0)

2、入：ROUNDUP(目标单元格，0) 得出来的结果是向上的整数，即：3.35=4

3、舍：ROUNDDOWN(目标单元格，0)得出来的结果是向下的整数，即：3.65=3

*公式中后面那个零表示取整，如果是1，表示小数点后1位，依次类推。

②TRUNC：数字截尾取整函数

TRUNC(目标单元格，0)

例：3.6=3 3.1=3

*公式中后面那个零表示取整，如果是1，表示小数点后1位，依次类推。

③INT：取整函数

(数字向下舍入到最接近的整数。这里的“向下”就是得到的结果比这个函数的参数要小，并且是整数。)

INT(目标单元格)

如：

INT(8.9)=8

INT(-8.9)=-9

因为8比8.9要小?-9比-8.9要小。

注：

函数TRUNC 和函数INT 类似，都返回整数。函数TRUNC 直接去除数字的小数部分，而函数INT 则是依照给定数的小数部分的值，将其向下舍入到最接近的整数。函数INT 和函数TRUNC 在处理负数时有所不同：

如：

trunc:

-1.7=-1

-1.1=-1

int:

-1.7=-2

-1.1=-2

找同一列重复数据：输入公式：=IF(COUNTIF(A:A,A2)>1,"重复","")

通配符查重公式（解决身份证号位数多不准确问题）

=COUNTIF(C:C,"*"&C1&"*")>1，此公式不重复显示FLASE ，重复显示TUCE。也可如下：

=COUNTIF(C:C,"*"&C1&"*")，此公式直接显示单元格中数据在C列出现的次数。

Excel表格中根据身份证号码自动填出生日期、计算年龄[1]

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位两种。所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。一、身份证号的位数判断在B2单元格输入如下公式“=LEN($A2)”，回车后即可得到A2单元格身份证号码的数字位数，如图1所示。LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。由于当初身份证输入时就是以文本形式输入的，所以用此函数正可以很方便地求到身份证号码的位数。

Excel里通过身份证号码计算性别

在EXCEL中利用身份证号码计算性别原理： 15位身份证，看最后一位，奇男偶女；18位的，看第17位数，也是奇男偶女。公式内的“B2”代表的是输入身份证号码的单元格。方法一： =IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女")) 公式含义：如果B2单元格中式15位的身份证号，则显示IF(MOD(MID(B2,15,1),2)=1,"男","女")的计算结果，否则，显示IF(MOD(MID(B2,17,1),2)=1,"男","女")的计算结果。方法二： 18位身份证号码中，第15～17位为顺序号，奇数为男，偶数为女。将光标定位在“性别”单元格中，然后在单元格中输入函数公式：=IF(VALUE(MID(B2,15,3))/2=INT(VALUE(MID(B2,15,3))/2),"女","男") 公式含义： ①函数公式中，MID(D2,15,3)的含义是将身份证中的第15～17位提取出来。 ②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。 ③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”是取整函数，如果是偶数，则前后相等；如果是奇数，则前后不相等。) ④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")的含义是若是“偶数”就填写“女”，若是“奇数”就填写“男”。

excel中用身份证号码生成性别

excel中用身份证号码生成性别、出生日期、计算年龄 (2010-06-23 22:28:15) 转载标签：杂谈 excel中用身份证号码生成性别、出生日期、计算年龄从身份证号码中自动生成性别和生日生成性别：（其中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中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。前6位数字是户籍所在地的代码，7－14位就是出生日期。第17位“2”代表的是性别，偶数为女性，奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。 STEp1，转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。 STEP2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入 “=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。如图1 。图1 通过上述方法，系统自动获取了出生年月日信息小提示：MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。 STEP3，判断性别“男女” 选中“性别”列的单元格，如D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2。

Excel如何根据身份证号码自动计算年龄

Excel如何根据身份证号码自动计算年龄方法1 1.打开要在excel中编辑的表格 2.如图所示，在身份证号后面的空格即年份一列第一格输入公式=MID(A2,7,4)，输入完成后按下enter键，A2指身份证号的单元格，数字7为数字开始位置，4为字符个数 3.按下enter键后，如图所示年份一栏已显示出出生年份 4.如图所示，选中已显示年份的一格，鼠标点击绿色框右下角的小方框并下拉至身份证号的最后一栏 5.如图所示，每个身份证号对应的年份都显示出来了 6.如图所示再在年龄一列第一格输入公式2018-MID（A2，7，4），按下enter键 7.即可看到年龄已被计算出来为21岁，如图所示鼠标点击绿色框右下角的小方框并下拉至身份证号的最后一栏 8.如图所示，用这种“自动填充”功能，就能让同类型单元格有同样的公式计算结果方法2 1.打开要在excel中编辑的表格,并选中年龄那一列的第一格 2.点击公式 3.再点击插入函数 4.在弹出来的对话框中在选择函数那一栏点击全部 5.下拉右侧的滚动条找到MID函数点击它

6.点击右下角的确定 7.在弹出来的对话框中点击第一格 8.然后点击Excel文档中的A2单元格 9.在第二格中输入数字7，表示数字开始位置 10.在第三格中输入数字4，表示字符个数为四个 11.最后单击确定 12.即可看到该身份证号的年份已经算出来了 13.然后在该公示前输入2018-即公式2018-MID（A2，7，4）按下enter键 14.即可看到年龄计算出来为21岁，下拉该单元格右下角的小黑方使下面的单元格拥有同样的计算格式 15.最后即可看到所有的年龄就被计算出来了

excel 怎样从身份证号码提取年龄和性别

excel 怎样从身份证号码提取年龄和性别- [电脑应用技巧] 版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明 https://www.360docs.net/doc/c618274948.html,/logs/50218662.html 因为自己需要，在网上找来了这个教程，函数真是好用的东西。这个教程很详细，不过我偷懒，因为自己觉得只需要看公式，所以用红字标记方便自己。。。。在EXCEL中如何利用身份证号码计算出生年月、年龄及性别在学校的人事管理中经常会遇到需要统计教职工的年龄的问题，但案头的原始资料只有身份证号码，其实这足够了。在EXCEL中，引用其内置函数利用身份证号码达到此目的比较简单。1、身份证号码简介（18位）： 1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。 2、确定“出生日期”： 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄” 应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式 “=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID （text,starl_num,num_chars）。 Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。 starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。 num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。确定“年龄”：

Excel表中身份证号码提取出生年月、年龄、性别的使用技巧[1]

Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 excle中当一个序列号变更，下面序列号自动变更的方法。浏览次数：298次悬赏分：0 |解决时间：2011-3-11 12:48 |提问者：kasure 问题补充：比如我编制了序列号001，002，003。。。。，然后我要是中间插入一行，比如在002和003之间插入一行，我下面的编号都要变动，如何实现这样的功能？最佳答案那我想知道如果你需要删除一行的话，下面的编号是否需要变动？如果都需要变动的话，你可以试试这样： 1、把序号列的单元格格式改成"000"(在设置单元格格式--自定义--类型那里可以改) 2、把序列号的单元格填上公式=row() 。如果表格上面有表头的话，你数数表头有多少行，在公式后面减去行数，例如有5行表头，公式就是=row()-5 当你插入行的时候把公式填上就可以了方法一： 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)&"-"&M ID(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(A 1,7,4)&"-"&MID(A1,11,2))

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(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,2007-VALUE(MID(B 2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据;

如何用EXCEL提取身份证号的出生日期并计算年龄

如何用EXCEL提取身份证号的出生日期并计算年龄身份证号码的数字都有其具体的意义，从这些数字中可以获得该号码所对应人员的出生户籍地、出生日期、性别等基本信息。利用EXCEL函数，可以很方便的提取身份证号码中的出生日期信息，并计算出年龄。工具/原料 ?电脑 ?OFFICE2003版或其他更高版本方法/步骤 1.身份证号码的结构和意义：（1）前6位是地址码，一般为出生上户口的时候户籍地的代码；（2）第7至第14位是出生日期码，是按年月日排列的；（3）第15至第17位是顺序码，是由公安机关编订的号码，用来区别同一户

籍地同年同月同日生的人，其中第17位是性别信息，奇数为男性，偶数是女性；（4）第18位即最后一位是校验码，由前面17位数字根据全国同一的算法进行计算得出。 2.打开示例EXCEL文件，该表有4个栏目：身份证号、姓名、出生日期、年龄。出生日期和年龄需要通过EXCLE进行提取和计算，该2列的单元格格式为“常规”格式。

3.提取出生日期需要使用的函数有两个，一个是DATE函数，一个是MID 函数。DATE函数的结构为DATE（year,month,day），其作用是将提取的数字变为日期格式进行显示。MID函数的结构为MID(text, start_num, num_chars)，其作用是从字符串中提取指定位数的字符，其中text是需要提取的字符所在的字符串，start_num是需要提取的字符在该字符串中的起始位置，num_chars是需要提取的字符的位数。

4.在C3单元格输入公式 “=DATE(MID(A3,7,4),MID(A3,11,2),MID(A3,13,2))”并下拉，其中MID(A3,7,4)用于提取年份，MID(A3,11,2)用于提取月份，MID(A3,13,2)用于提取日期。 5.不需要精确到日期的年龄计算可以直接用需计算的年份减身份证号的年份来得出。如要算所有人在2016年时的年龄，在C3单元格输入公式“=2016- MID(A3,7,4)”即可。

2种方法,在身份证号中提取出生年月日

2种方法，在身份证号中提取出生年月日教程背景：公司最近新入职了20名员工，已知每个员工的身份证号，现需要将员工的出生年月日统计出来，纳入公司的福利数据库，以便在员工生日当天为员工发放生日福利。员工身份证号▼ 方法一：函数法 MID函数与TEXT函数的配合使用） MID函数：用于从字符串的指定位置开始，提取特定数目的字符串。 Text函数：表示将数值转化为自己想要的文本格式。

身份证号码为18位，第7位到第14位这8个数字为出生年月日。步骤一、使用公式“=MID(C2,7,8)”就是从C2单元格的第7位开始，提取8位数字，结果为'19870507'。步骤一得到的结果▼ 步骤二、再使用公式“=TEXT(MID(C2,7,8),'0-00-00')，将这个字符串变成 '0-00-00'的样式，结果为'1987-05-07'。步骤二得到的结果▼

步骤三、现在'1987-05-07'只是有了日期的外形，但是本身还是文本型的，变成日期型的还需要在最前面加两个负号“--”，通过两次“负”的运算，就成为了真正的日期格式了。最终公式为“=--TEXT(MID(C2,7,8),'0-00-00')”。步骤三得到的结果▼

可能细心的读者发现了，步骤二和步骤三得到的结果看起来是一样的啊，为什么要多此一举呢？这个地方用“=”公式检验一下就可以知道是不是一样的呢。公式验证▼

由此可见，步骤二的结果和步骤三的结果完全不一样，步骤二是文本格式，步骤三是日期格式，当我们把年月日变为日期格式后，在使用其他数据透视表或者其他工具时，日期格式的数据才能被识别进行相应的运算。方法二：分列直接使用工具栏数据-分列步骤一、利用分列选择“固定宽度”。固定宽度▼

从身份证号码中提取出生日期性别年龄公式-身份证号性别公式

1、从身份证号码中提取出生日期性别年龄公式： =DATE(MID(A1，7,4), MID(A1，11,2), MID(A1，13,2)) 2、从身份证号码中提取出性别公式： =IF(MOD(MID(A1,17,1),2)=1,"男","女") ②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式“=MI D(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID 3、确定“年龄”： “出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“=INT((TODAY()-E2)/3 65)”即可计算出“年龄”。关于这个函数公式的具体说明 ④计算以后可能有多位小数，可以用【减少小数位数】按钮，将年龄的数值变成“整数”，也可在公式= (TODAY()-E2)/365中再嵌套一个“INT”函数取整数，即“ =INT((TODAY()-E2)/365)”，这样就会自动将后面的小数去掉，只保留整数部分。操作效果如下图：

4、确定“性别”：每个人的性别可以利用“身份证号码”进行判断，18位身份证号码中，第15～1 7位为顺序号，奇数为男，偶数为女。

=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")的含义是若是“偶数”就填写“女”，若是“奇数”就填写“男”。操作效果如下图：（注：文档可能无法思考全面，请浏览后下载，供参考。可复制、编制，期待你的好评与关注）

用Excel从身份证号码中提取信息(年龄、性别、出生地)

用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文件，另存为××文件，即可使用。谢谢你的使用。水晶六彩

身份证号码计算年龄

在电子表格中，如何设置公式通过身份证号码计算出年龄。比如在a列中输入身份证号码，在b列置换出出生年月，c列计算出周岁5222428198602113112 1986-02-11 24 最佳答案设A1文本格式的18位身份证号,B1输入如下公式,即可算出当天的年龄. =YEAR(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1900&"岁"&MONTH(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1&"月"&DAY(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))&"天" 如要算指定日期的年龄,则设A1文本格式的18位身份证号,B1为指定日期,在C1输入公式如下: =YEAR(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1900&"岁 "&MONTH(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1&"月 "&DAY(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,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)),"身份证错")) 4、假设身份证号在A2,在B2单元格输入公式：其中2011改为当前年： =2011-IF(LEN(A2)=18,MID(A2,7,4),"19"&MID(A2,7,2)) 5、假设身份证号在A2中，则可用下面的公式算出其周岁年龄： =DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,(LEN(A2)=18)*2+6),"#-00-00") ,TODAY(),"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表格中输入身份证号如何自动提取性别和出生年月如输入大批量的个人信息。(例：输入姓名、性别、身份证号、出生年月日、地址等等)，特别是在输入身份证号之后还要输入一些出年月日、性别、其时这些都已经在身份证号里面体现出来了，所以我想有没有办法提取出来。经过实践体验，现已经解决了这个问题，这样减少了不少时间，对于一两个人信息的输入这没什么，而对于成百上千的要输入来说，就是关键了。例如：序号姓名身份证号码性别出生年月说明：公式中的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,C ONCATENATE(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从身份证号码中提取出生日期

如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如：从身份证420821************中提取出生日期来，如何快速得出？只需使用语句：=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)) 【A1是身份证号码所在单元格】 date()函数是日期函数；如输入今天的日期=today() 那么，mid函数是什么东东呢？ MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串；Start_num 为文本中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ，以此类推；Num_chars 指定希望MID 从文本中返回字符的个数。对身份证号码分析下就知道：420821************，出生日期是1992年2月6日；也就是从字符串（420821************）的第7位开始的4位数字表示年，从字符串的第11位开始的2位数字表示月，字符串的第13位开始的2位数字表示日。呵呵，强悍吧！ Excel中利用身份证号码（15或18位）提取出生日期和性别需要的函数： LEN(C6)=15：检查C6单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位； INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。 RIGHT:返回文本字符串最后一个字符开始指定个数的字符； MID：返回文本字符串指定起始位置起指定长度的字符，MID(C6,7,2)表示：在C3中从左边第七位起提取2位数；"19"&MID(C6,7,2)表示：在C3中从左边第七位起提取2位数的前面添加19； …… &""&表示：其左右两边所提取出来的数字不用任何符号连接； &"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年 mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了。

Excel从身份证号中提取出生年月和年龄

假设数据在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位开始的

Excel表格中从身份证号中提取出生年月日图文教程

Excel表格中从身份证号中提取出生年月日图文教程例：在C3中输入身份证号码,要在其他单元格中自动显示出这个人的出生年月日的公式(这个公式同时适用15位和18位) =IF(LEN(C3)=18,IF(ISBLANK(C3),"",CONCA TENATE(MID(C3,7,4),"年",MID(C3,11,2),"月",MID(C3,13,2),"日")),IF(ISBLANK(C3),"",CONCATENA TE("19",MID(C3,7,2),"年",MID(C3,9,2),"月",MID(C3,11,2),"日"))) 红色内容就是公式，复制到表中就可以用了说明: 一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。 18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。例如，某学生的身份证号码(15位)是5101259309273810，那么表示1993年9月27日出生，性别为男。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。二、提取个人信息这里，我们需要使用IF、LEN、MOD、 MID、DATE等函数从身份证号码中提取个人信息。如图一所示，其中学生的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在E列。 1. 提取出生年月信息由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D3单元格中输入公式“=IF(LEN(C3)=15,MID(C3,7,4),MID(C3,9,4))”，其中： LEN(C3)=15：检查C3单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。 MID(C3,7,4)：从C3单元格中字符串的第7位开始提取四位数字，本例中表示提取15位

EXCEL利用身份证号码计算年龄以及年龄分段的技巧

在EXCEL中如何利用身份证号码计算出生年月年龄及性别 1、身份证号码简介（18位）： 1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。 2、确定“出生日期”： 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式 “=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID （text,starl_num,num_chars）。 Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。 starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。 num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图：

3、确定“年龄”： “出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。关于这个函数公式的具体说明： ①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。操作格式是TODAY（）。 ②用TODAY（）-E2，也就是用当前日期减去出生日期，就可以计算出这个人的出生天数。 ③再除以“365”减得到这个人的年龄。 ④计算以后可能有多位小数，可以用【减少小数位数】按钮，将年龄的数值变成“整数”，也可在公式= (TODAY()-E2)/365中再嵌套一个“INT”函数取整数，即 “ =INT((TODAY()-E2)/365)”，这样就会自动将后面的小数去掉，只保留整数部分。操作效果如下图：还有一种函数（datedif）可以解决这个问题：这个函数用于计算两个日期之间的天数、月数或年数。语法：DATEDIF(start_date,end_date,unit) start_date为一个日期，它代表时间段内的第一个日期或起始日期。 end_date为一个日期，它代表时间段内的最后一个日期或结束日期。