在工作中,我们会经常涉及关于身份证号码的excel处理问题,今天小编就给大家介绍一下“身份证号码与年龄”的处理方法。
1. 不涉及到月份的处理(模糊)
问题可以理解为:提取出生年份,再用当前年份减去被提取的年份
相关函数语法:MID(字符串选择, 字符开始位置, 字符个数)
案例:需要求出下表中每个人的年龄。
step1:在C2单元格中,输入函数:=2019-MID(B2,7,4)
step2:指针放在单元格的右下角,变成“+ ”后下拉即可填充整列。
2 涉及到月份的处理(精确)
问题可以理解为:提取出生日期,再用当前日期减去被提取的日期
涉及函数语法:
MID(字符串选择, 字符开始位置, 字符个数)
TEXE(字符串,格式)
DATEDIF(起始日期,结束日期,类型)
案例:需要求出下表中每个人的年龄。
step1:C2单元格中输入函数,=MID(B2,7,8)。提取出生时间。
step2:使用TEXT函数将出生时间格式改为标准日期形式。即在D2单元格中输入函数,=TEXT(C2,”0000-00-00”)”
step3:DATEDIF提取时间段. 在E2单元格中输入函数,=DATEDIF(D2,”2019-08-28”,”y”)
step4:同样“+”下拉填充整列,大功告成!
总结——“身份证号码与年龄”的处理方法
① 不涉及到月份的处理(模糊):
第一,提取出生年份,再用当前年份减去被提取的年份
第二,涉及函数:MID(字符串选择, 字符开始位置, 字符个数) 👉👉提取字符串
②涉及到月份的处理(精确):
第一,提取出生日期,再用当前日期减去被提取的日期
第二,涉及函数(分步骤):
MID(字符串选择, 字符开始位置, 字符个数)👉👉提取字符串
TEXE(字符串,格式)👉👉转换字符串格式
DATEDIF(起始日期,结束日期,类型)👉👉按不同单位计算时间段
(一步到位):
DATEDIF(TEXT(MID(字符串,7,8),”#-00-00”),TODAY(),”y”)
欢迎大家关注【头条送书小王子】雷哥office,每周免费送书/分享职场办公神技,希望我的分享可以提高大家工作效率,从此不加班~
HR必备技能——从身份证号中提取出生年月、性别等实用技巧!
身份证,是我们每个人必备的证件,其实身份证号中隐藏着大量的密码,例如,出生年月、性别等……那么在Excel中如何提取这些信息呢?一、出生年月。HR必备技能——从身份证号中提取出生年月、性别等实用技巧!方法:在目标单元格中输入公式:=TEXT(MID(C3,7,8),”00-00-00″)。解读:1、利用Mid提取C3单元格中从第7位开始,长度为8的字符。2、利用Text函数将提取的字段设置为格式“00-00-00”。其实除了利用函数提取出生年月之外,还可以用快捷键的方式提取。HR必备技能——从身份证号中提取出生年月、性别等实用技巧!方法:1、在目标单元格的第一个单元格中输入对应的出生年月。2、选定所有目标单元格,包括数据数据的单元格。3、快捷键Ctrl+E填充其它单元格。解读:1、此方法主要应用于不会使用公式的情况。2、如果对提取的值格式不满意,可以通过自定义格式的方式进行再设置哦!二、提取性别。HR必备技能——从身份证号中提取出生年月、性别等实用技巧!方法:在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),”男”,”女”)。解读:1、身份中号的第17位代表性别,如果为奇数,则为“男”,偶数则为“女”。2、利用Mid函数提取第17位的值,利用Mod函数求余,然后利用IF函数判断,如果余数为1(暨奇数),则返回“男”,如果余数为0(暨偶数),则返回“女”。三、提取年龄。HR必备技能——从身份证号中提取出生年月、性别等实用技巧!方法:在目标单元格中输入公式:=DATEDIF(D3,TODAY(),”y”)。解读:1、年龄就是当前年份减去出生年月的年份,而在Excel中正好有一个函数是计算是两个时间差的,此函数就是系统隐藏函数Datedif。2、Datedif函数的语法结构:=Datedif(开始日期,结束日期,统计方式),其中常见的统计方式有“Y”、“M”、“D”。分别为:“年”、“月”、“日”。四、提取退休时间。HR必备技能——从身份证号中提取出生年月、性别等实用技巧!方法:在目标单元格中输入公式:=EDATE(D3,MOD(MID(C3,17,1),2)*60+660)。解读:1、当前的退休年龄为男:60岁,女55岁。2、Edate函数的作用是:返回指定日期之前或之后的月数。语法结构:=Edate(日期,指定的月份)。3、首先利用Mod(Mid(C3,17,1),1)判断当前性别,如果为“男”,则为1*60+660=720个月,也就是60年;如果为“女”,则为0*60+660=660,也就是55年。4、利用Edate函数在出生年月的基础上加上60年或55年得到退休日期。结束语:一个身份证号,竟然包含着出生年月,性别等多个信息,对于提取技巧,你Get到了吗?如果亲有更好的关于身份证号码的应用技巧,不妨告诉小编活在留言区留言讨论哦!亲的支持是小编不断前进的动力哦,自己学习的同时别忘了“点赞评”哦。
本文来自投稿,不代表天一生活立场,如若转载,请注明出处:http://tiyigo.com/it/35009.html