作为HR,不得不会的函数公式 excel技巧
一、根据身份证号码提取出生年月日
输入公式:=TEXT(MID(B2,7,8),'0-00-00')*1
身份证号码的第7-14位为出生年月日
MID(B2,7,8)部分从B2单元格身份证号码中第7位开始提取,提取8位,返回'19900518';
用函数TEXT返回'1990-05-18',是文本格式,*1转为真正的日期。
二、根据出生年月日获取年龄
输入公式:=DATEDIF(C2,TODAY(),'y')
三、根据身份证号码判断性别
输入公式:=IF(ISODD(MID(B2,17,1)),'男','女')
身份证号码第17位为奇数,则是男;为偶数,则是女。
函数ISODD:如果数字为奇数则返回TRUE。
四、计算合同到期日
根据劳动合同签订日和期限,计算合同到期日
输入公式:=EDATE(B2,C2)-1
函数EDATE(开始日期,之前/之后的月数)
五、计算退休日
以男60岁退休,女55岁退休为例:
输入公式:=EDATE(C2,IF(D2='男',60,55)*12)
IF(D2='男',60,55)部分如果性别为男,返回60,否则返回55;
函数EDATE是按月进行计算,所以*12是将年转换成月;
出生日期加上转换的月数,返回的日期就是退休日。
六、员工生日提醒
输入公式:=TEXT(TEXT(C2,'m-d')-TODAY(),'还有0天生日;;今天生日')
TEXT(C2,'m-d')部分将日期转为月-日形式,返回'5-18';
TODAY()返回当前日期2020-11-5,两日期相减就是相差天数;
天数大于0显示还有多少天生日,小于0不显示,等于0显示今天生日。
TEXT(数据,大于0显示值;小于0显示值;等于0显示值)
七、计算工龄
根据员工的入职日期,计算工龄
输入公式:=DATEDIF(B2,TODAY(),'Y')
用函数DATEDIF返回两日期间的整年数。
八、计算年休假
使用函数IF:
输入公式:=IF(C2>=20,15,IF(C2>=10,10,IF(C2>=1,5,0)))
使用函数VLOOKUP:
输入公式:=VLOOKUP(C2,{0,0;1,5;10,10;20,15},2)
使用函数LOOKUP:
输入公式:=LOOKUP(C2,{0,1,10,20},{0,5,10,15})
使用函数SUM:
输入公式:=SUM(5*(C2>={1,10,20}))
觉得对你有用那就点个关注吧,每天持续更新好的内容