excel身份证算出生日期

推荐生活 2019-01-31 09:34:13  2890

本文收集整理关于excel身份证算出生日期的相关议题，使用内容导航快速到达。

内容导航：

Q1:Excel中，如何利用身份证快速提取性别、年龄和出生日期？
Q2:身份证号中的出生日期如何用excel批量提取出来？
Q3:excel怎么从身份证号中提取出生日期
Q4:Excel如何从身份证号码中提取计算出生日期、性别及年龄呢？

Q1：Excel中，如何利用身份证快速提取性别、年龄和出生日期？

今天教你一下身份证相关的所有内容。

一、身份证相关

1、身份证的输入

在excel中输入大于11位数字，会以科学记数法显示。而大于15位，后面的数字全转换为0，所以不能直接在excel中输入身份证号。

解决方法：先把该列设置为文本型格式，或输入前先输入单撇()再输入身份证号码

2、身份证的导入

从word、网页、数据库中复制含身份证信息的表格时，如果直接粘贴到excel中，身份证码列同样会后3位变成0。

解决方法：同1粘贴或导入前把excel表中存放身份的列设置为文本类型。

3、身份证的分列

如下图所示的员工信息在一列中，我们可以用分列的方式分隔成多列，但分列后身份证号码后3位会变成0。

解决方法：在分列的第3步，选取身份证列，选取文本类型即可。

4、身份证号长度验证

在输入身份证号码时，数字个数看起来很费劲。用数据有效性可以限制身份证号码输入必须是18位。

解决方法：选取输入单元格 - 数据 - 有效性 - 允许：文本长度，输入18

单元格A1中是身份证号码

5、提取生日

=TEXT(MID(A1,7,8),"0-00-00")

6、提取年龄

=YEAR(TODAY())-MID(A1,7,4)

7、提取性别

=IF(MOD(MID(A2,17,1),2),"男","女")

8、提取出生地

9、提取属相

=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

10、提取星座

=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"**";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)

Q2：身份证号中的出生日期如何用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列所有身份证号，选择数据菜单，单击分裂命令，打开分列向导：

选择固定宽度，单击下一步，在日期前后分别建立分列线，单击下一步：

选择中间日期数据列，将列数据格式设置成日期型，单击完成，大功告成。

完成效果如下：

好累啊，给个大拇指好不好，(*^__^*) 嘻嘻……

WWw.YijIt∽ao.cOm

Q3：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)),"号码有错"))。出现这样非出生日期标准格式