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

在EXCEL中利用身份证号码提取生日及年龄 LEN：返回文本字符串的个数,本例中用来计算身份证的位数 MID：返回文本字符串指定起始位置起指定长度的字符,本例中用来计算身份证号中出生日期的字符； MID(C6,7,2)表示：在C3中从左边第七位起提取2位数； INT：返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数. RIGHT：返回文本字符串最后一个字符开始指定个数的字符,本例中来计算身份证号最后一位数. 19MID(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)日,19MID(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位数,就执行19MID(B2,7,2)年MID(B2,9,2)月MID(B2,11,2)日)含义与18位相似．只是在年MID(B2,7,2)前面要加上＂19＂ 方法二： =IF(LEN(B2)=15,19MID(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位的时，就显示19MID(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单元格中计算出的结果是“1986-02-02”。 二.计算年龄 “出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式 =INT((TODAY()-D2)/365) 即可计算出“年龄”。D2是年龄单元格。 函数说明： TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无