用EXCEL从身份证号中提取信息并计算出退休年龄【百科全说】

身份证号码隐藏着你的地区,出生日期，性别信息，如果建的表中有身份证号码那么出生日期、性别、年龄、工龄、退休日期就不用单独录入了，利用公式就可以。这样方便录入提高工作效率。
目前我们用的都18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。18位为效验位。
了解了上面的这些内容，接下来说说18位身份证号码的信息提取。

操作方法

01

首选录入信息时，录入身份号那一列全部选中，设置单元格格式中选“文本”，避免出现图2这种情况。
02

同上，D列全部选中，设置单元格格式中选“日期”。如图。避免出现一串不能理解的数字。
03

在B3单元格输入以下公式，提取性别： =IF(MOD(MID(C3,17,1),2),"男","女") 公式中的MID(C3,17,1)部分意思是，从C3单元格第17位数字提取。计算结果是"3"。奇数为男，偶数为女。 MOD函数用于返回两数相除的余数，此例中的MOD函数第二参数使用2，就是用于判断MID函数的结果是否能被2整除，能够整除返回0，否则返回1。 IF函数根据MOD函数的计算结果，返回指定内容。如果MOD函数的计算结果为 1，IF 函数将返回“男”；如果MOD函数的计算结果为0，则返回“女”。
04

出生日期的提取使用以下公式来完成： =--TEXT(MID(C3,7,8),"0-00-00") 公式中的MID(C3,7,8)部分，用于提取出C3单元格中身份证号码的第7-14位。计算结果为"19850430"，用TEXT函数将这个公式结果强制变成"1985-04-30"，前面加了两个减号用于减负运算，将文本"1985-04-30"变成真正的日期格式。
05

接下来看一下年龄的计算，E3单元格输入以下公式： =DATEDIF(D3,TODAY(),"y") DATEDIF函数是一个隐藏函数，没有出现在函数列表中，Excel中的公式自动完成功能也不会自动生成这个函数名称，甚至在多个版本的帮助文件中都找不到这个函数的踪影。这个函数主要用于计算两日期相差年月日数，利用该函数可计算相差的天数、月数和年数。对于DATEDIF函数的使用方法，咱们可以这样理解： DATEDIF(起始日期,结束日期,指定的汇总方式) 第三参数为所需信息的返回时间单位代码。各代码对应的含义如下：第三参数代码函数返回值 "y" 时间段中的整年数。 "m" 时间段中的整月数。 "d" 时间段中的天数。 "md" 起始日期与结束日期天数的差。忽略日期中的月和年。 "ym" 起始日期与结束日期月数的差。忽略日期中的日和年。 "yd" 起始日期与结束日期天数的差。忽略日期中的年。在这个公式中，DATEDIF函数返回D3单元格的出生日期到当前日期（TODAY()）的整年数，也就是实际年龄。如果将上面这条公式稍作改动，换成=DATEDIF(入司时间,TODAY(),"y")，那就是咱们常用到的工龄计算公式了。
06

在F3单元格用下面这个函数计算退休年龄： =EDATE(D3,660+(B3="男")*60) EDATE函数用于返回指定日期之前或之后的月数。在本例中，咱们利用了D3单元格已经计算出来的出生日期和B3单元格性别信息作为计算条件。用D3单元格的出生日期作为起始日期，指定的月数是660+(B3="男")*60这串字符初看起来可能有点费解，咱们分解一下： 660，也就是12个月/年*55年（女性退休年龄）；(B3="男")*60这里表示如果B3单元格性别是“男”，就再加上60个月（12个月/年*5年），否则就是660。可以这样理解：如果B3单元格性别是“女”，按=EDATE(D3,660)计算退休日期，如果B3单元格性别是“男”，则按=EDATE(D3,660+60)计算退休日期。

用EXCEL从身份证号中提取信息并计算出退休年龄

操作方法

相关推荐