12.22 从身份证号码中提取员工的生日信息

巧用excel将身份证号码转换得到出生日期及性别

早期的身份证号码为15位数字，现在使用的身份证号码为18位数字，它们的含义如下：

（1）15位：1-6位为地区代码，7-8位为出生年份（2位），9-10位为出生月份，11-12位为出生日期，第13-15位为顺序号，这3位奇数为男，偶数为女。

（2）18位：1-6位为地区代码，7-10位为出生年份（4位），11-12位为出生月份，13-14位为出生日期，第15-17位为顺序号，同上，第18位为效验位。

在审核参保人员是否符合领取补助的年龄时，需要通过依据身份证号码，自动提取出出生日期、性别、年龄等信息。

在excel中巧用以下几个公式，就可以轻松得到这些信息。

注：A列中输入的是身份证号码。

1、出生日期

=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),"/",MID(A1,9,2),"/",MID(A1,11,2)),CONCATENATE(MID(A1,7,4),"/",MID(A1,11,2),"/",MID(A1,13,2)))

2、性别

=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")))TODAY()),"错误",IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")))))

3、至审核时候实际年龄

=IF(ISERROR(1*(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00")))TODAY()),"错误",DATEDIF(TEXT(MID(A3,7,6+(LEN(A3)=18)*2),"#-00-00"),TODAY(),"y")))

汇总⾝份证提取出⽣⽇期⽤这3种⽅法⾜够

⾝份证提取⽣⽇的⽅法很多，常见的有各种函数，如mid函数，text函数，Concatenate函数等等...

如果不想使⽤函数可以做到吗？

⼀样可以，分列法分列法 （五星强推）

如果你的版本是2013及以上，还可以使⽤快速填充法快速填充法 （四星推荐）

=============

1、分列法

顾名思义，将出⽣⽇期从⾝份证中分离出来作为单独的⼀列并设置为⽇期格式

包括两个步骤：数据分列 格式设置

步骤：

① 复制⾝份证号码到⽣⽇列 ▼

② 『数据』 - 『分列』- 选固定宽度 ▼

③ 建⽴分列线，在出⽣⽇期前后加两条分列线 ▼

④ 将出⽣⽇期前后的数据设置【不导⼊此列】

出⽣⽇期设置为⽇期格式，动图演⽰ ▼

⑤ 效果如下 ▼

⑥ 当前格式已为⽇期格式，可⾃由更改⽇期显⽰⽅式 ★

点评：点评：分列法既⽅便⼜格式标准，能任意更改⽇期显⽰⽅式，也不挑版本，好⽤！分列法既⽅便⼜格式标准，能任意更改⽇期显⽰⽅式，也不挑版本，好⽤！

=============

2、快速填充法 （适⽤于Excel2013及以上版本）

① 在第⼀⾏输⼊第1个⾝份证号码中的出⽣⽇期信息，然后按ctrl E键（快速填充快捷键 ）向下填充

② 单元格格式设置⾃定义格式 0000-00-00

点评：点评：快速填充法只需⼿动输⼊第⼀⾏，打个样后系统⾃动识别，很快捷！快速填充法只需⼿动输⼊第⼀⾏，打个样后系统⾃动识别，很快捷！

但是只适⽤于Excel 2013版本及以上且格式为⾮标准⽇期格式

=============

3、Mid 函数

⾝份证号中第7位-第15位为出⽣年⽉⽇信息

使⽤mid函数从第7位开始提取，共提取8个字符

① 输⼊公式=mid（b2,7,8） ▼

② 单元格格式设置⾃定义格式 0000-00-00

点评：mid函数参数少，使⽤⽅便，但函数参数少，使⽤⽅便，但格式为⾮标准⽇期格式格式为⾮标准⽇期格式=============

4、Text Mid 函数

如何通过函数提取身份证中的出生日期

第一篇：如何通过函数提取身份证中的出生日期

如何通过函数提取身份证中的出生日期

某公司要改善公司福利，决定在每一个员工过生日时送一个深日蛋糕，为此公司经理要求人事部主管将员工的生日统计下来。如果通过逐一询问员工生日的方式来统计将非常麻烦，此时该主管可以在已有员工身份证信息的基础上，通过“CONCATENATE”函数来提取员工的生日日期。具体的操作步骤如下。

①选中要输入公式的区域，在编辑栏中输入“＝CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)”。

②按下【Ctrl】＋【Enter】组合键即可将所有身分证中的出生日期提取出来。

“CONCATENATE”函数的操作对象必须是文本，它的作用就是将几个文本字符串合并为一个文本字符串。“MID(F1,7,4)”代表的是在单元格F1中从第七位开始向右取两个字符，CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)代表的是将提取出的字符串与“年”、“月”、“日”合并为一个字符串。

第二篇：EXCEL提取身份证出生日期

EXCEL提取身份证出生日期、性别、检验身份证号码正确性

中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为： １５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。

１、提取籍贯地区的行政区划代码（A2为身份证号，下同）１５与１８位通用：=LEFT(A2,6)

如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。２、提取出生日期信息

Excel如何从身份证号中提取出生年月日[详细版]

在使用excel的时候，经常会遇到从身份证号中提取出生年月日这样的问题，今天小编就带大家一起来从身份证号中提取出生年月日，我们首先需要理解从身份证中提取出生年月日的原理，我们的身份证是一个18位的数字，其中第7位到第8位就代表着我们的出生年月日，以下小编介绍的三种方法都是以此为基础。

工具/原料

•

电脑一台

• 方法一

.

1

.

这里小编在B列有三个身份证号码，需要在C列中让excel自动填充出生年月日，这里小编先进行介绍方法一，方法一主要使用的是MID函数，如下图：

.

.

. 2

.

在C列对应的单元格中我们输入公式“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”，其中MID函数作用为从指定的字符串中截取出指定数量字符，例如公式中的“MID(B3,7,4)”,第一个参数表示B3单元格中的身份证号，7表示从第7位开始计算，4表示从第7位开始共截取几位数字，其中公式中的“&”表示拼接字符串，如下图：

.

.

.

3

.

回车之后我们得到使用MID函数和&拼接符自动计算得出的出生年月日，如下图：

.

.

.

4

.

然后我们将公式填充到其他单元格，得到所有身份证的出生年月，如下图：

.

. END

.

方法二

.

1

.

这里小编介绍方法二，主要使用的是MID函数和TEXT函数，如下图：

.

.

.

2 .

我们在对应的单元格输入“=TEXT(MID(B3,7,8),"0000-00-00")”，其中MID函数作用为从指定的字符串中截取出指定数量字符，TEXT函数作用为将制定的字符转化为自己想要的格式文本，如下图：

.

.

.

3

.

回车之后，我们就可以看到我们想要的格式出生年月，如下图：

.

.

.

4

.

然后将公式填充剩下的单元格，我们就得到了全部的出生年月，如下图：