手机版
当前位置: 首页 常识

如何查询退休年龄 Excel——利用身份证号码获取性别、出生年月、年龄和退休日期

时间:2024-07-19 12:30:54

一、任务需求

根据身份证号码分别获取员工的性别、出生年月、年龄和退休日期。

员工信息表

二、任务难点

唯一知道的信息只有身份证号码

三、解决思路

1、身份证号码的第17位表示性别信息,单数是“男性”,双数是“女性”。

2、身份证号码的第7位到14位是出生年月。

3、用当前日期减去出生年月可以获取年龄。

4、男性退休年龄是60岁;女性干部退休年龄是55岁,非干部女性退休年龄是50岁。

四、具体方法

(一)获取性别

由于身份证号码的第17位表示性别信息,单数是“男性”,双数是“女性”。

1、在D2单元格总输入公式:=IF(MOD(MID(B2,17,1),2),"男","女"),并向下填充。

2、公式解析

公式MID(B2,17,1):从身份证号码中提取出第17位字符。

公式MOD(MID(B2,17,1),2):计算提取出的第17位字符与2相除的余数

公式=IF(MOD(MID(B2,17,1),2),"男","女"):如果相除余数为“1”,则单数,返回“”;如果余数为“0”,则是双数,返回“”。

获取性别

(二)获取出生年月

由于身份证号码的第7位到14位是出生年月。

1、在E2单元格总输入公式:=TEXT(MID(B2,7,8),"0000-00-00"),并向下填充。

2、公式解析

公式MID(B2,7,8):从身份证号码中第7位开始,提取出8个字符,即“19911203”。

公式=TEXT(MID(B2,7,8),"0000-00-00"):把提取出的出生年月字符串“19911203”,转为日期格式“1991-12-03”。

获取出生年月

(三)获取年龄

用当前日期减去出生年月可以获取年龄。

1、在F2单元格总输入公式:=DATEDIF(E2,TODAY(),"Y"),并向下填充。

2、公式解析

公式TODAY():获取到当前日期“2023-01-24”。

公式= DATEDIF(E2,TODAY(),"Y"):获取出生年月当前日期之间的年月日间隔,指定类型为“Y”,返回完整的年份。

获取年龄

(四)获取退休日期

由于男性退休年龄是60岁,女性干部退休年龄是55岁,非干部女性退休年龄是50岁。

1、在G2单元格总输入公式:

=EDATE(E2,IF(D2="男",720,IF((D2="女")*(C2="干部"),660,600))),并向下填充。

2、设置单元格格式——日期格式

由于使用EDATE函数计算出的日期是序列号,需要将其格式设置为日期格式才能显示为正常的日期。

(选中退休日期列——开始——数字——自定义——类型——“yyyy-mm-dd”)

设置单元格格式——日期格式

3、公式解析

公式IF(D2="男",720,IF((D2="女")*(C2="干部"),660,600)):判断性别是不是“男”,如果是则返回720(60年),如果不是性别“男”,则判断性别是不是“女”,职务是不是“干部”,如果是则返回660(55年),否则返回600(50年)。

判断流程

公式=EDATE(E2,IF(D2="男",720,IF((D2="女")*(C2="干部"),660,600))):求出在出生年月加上退休月数的日期,即“1991-12-03”加上“720”个月是“2051-12-03”。

获取退休日期

以上就是如何利用身份证号码获取性别、出生年月、年龄和退休日期的方法,你学“废”了吗?