知之为知之不知为不知:在EXCEL中利用身份证号码提取生日及年龄

在EXCEL中利用身份证号码提取生日及年龄  

LEN：返回文本字符串的个数,本例中用来计算身份证的位数

MID：返回文本字符串指定起始位置起指定长度的字符,本例中用来计算身份证号中出生日期的字符； MID(C6,7,2)表示：在C3中从左边第七位起提取2位数；

INT：返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数.

RIGHT：返回文本字符串最后一个字符开始指定个数的字符,本例中来计算身份证号最后一位数.

"19"&MID(C6,7,2)表示：在C3中从左边第七位起提取2位数的前面添加19；……

&""&表示：其左右两边所提取出来的数字不用任何符号连接；

&"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了。

一．提取生日

1、18位身份证号码及提取方法：

1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。

公式内的“B2”代表的是输入身份证号码的单元格。

18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。

方法一：

=DATE(mid(B2,7,4),mid(B2,11,2),mid(B2,13,2))

函数说明：

DATE是表示显示日期的数字，就是下面的年、月、日

mid(B2,7,4）表示取B2单元格中的第七位起的四位数是年，

mid(B2,11,2)表示取B2单元格中的第十一位起的二位数是月，

mid(B2,13,2)表示取B2单元格中的第十三位起的二位数是日，

方法二：显示yyyy-mm-dd格式

=mid(B2,7,4)&"-"&mid(B2,11,2)&"-"&mid(B2,13,2)

函数说明：

MID函数用于从数据中间提取字符，它的格式是：MID（text,starl_num,num_chars）。

Text是指要提取字符的文本或单元格地址。

starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。

num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。

多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。

2、15位身份证号码及提取方法

1～6位为行政区划编码，7——12是出生日期（年份占用2位），最后3位是序列号序列号男性为奇数，女性为偶数。  

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

=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2)

3、15位18为位混合提取方法

方法一：

=IF(LEN(B2)=18,MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日","19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日")

函数说明：

如果B2单元格是一个18位数IF(LEN(B2)=18)那么从第7位数开始得到的4位数就是年份MID(B2,7,4)后面再加上文字&”年”,月日依此类推,否则, B2单元格为15位数,就执行"19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日")含义与18位相似．只是在年MID(B2,7,2)前面要加上＂19＂

方法二：

=IF(LEN(B2)=15,"19"&MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2),MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2))

函数说明：

当其检查到B2单元格中的数据是15位的时，就显示"19"&MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2)的计算结果，否则就显示MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)的计算结果。如：若B2单元格中是130728870501323，计算出的结果是“1987-05-01”；若B2单元格中是130728198602026621，计算出的结果是“1986-02-02”。

二.计算年龄

“出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式

=INT((TODAY()-D2)/365)

即可计算出“年龄”。D2是年龄单元格。

函数说明：

①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。操作格式是TODAY（）。

②用TODAY（）-D2，也就是用当前日期减去出生日期，就可以计算出这个人的出生天数。

③再除以 “365”减得到这个人的年龄。

④计算以后可能有多位小数，可以用[减少小数位数]按钮，将年龄的数值变成“整数”，也可在公式= (TODAY()-D2)/365中再嵌套一个 “INT”函数取整数，即“ =INT((TODAY()-D2)/365)”，这样就会自动将后面的小数去掉，只保留整数部分。