Excel技巧应用篇：公司HR，不得不会的函数公式

一、员工考勤表公式

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)

=COUNTIF(B9:AE9,"调")

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

二、根据身份证号码提取出生年月日

输入公式：

=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是将年转换成月；

出生日期加上转换的月数，返回的日期就是退休日。

七、计算工龄

根据员工的入职日期，计算工龄

输入公式：

=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}))

九、计算合同到期日

下图为公司员工的合同签订日与合同期限，要怎么才能计算出合同到期日呢？

输入公式：

=EDATE(B2,C2*12)-1

C2*12是将年乘以12转换成月；

-1是合同从签订日有效，所以在到期日减1天。

2、计算退休日

如下图，根据员工的出生日期和性别计算退休日，以男60岁退休，女55岁退休为例：

输入公式：

=EDATE(B2,IF(C2="男",60,55)*12)

IF(C2="男",60,55)部分是如果性别为男，返回60，否则返回55；

函数EDATE是按月进行计算，所以*12是将年转换成月；

出生日期加上转换的月数，返回的日期就是退休日。

十、计算出生日期

如下图，根据生日日期和年龄，倒推出生日期，该怎么写公式呢？

输入公式：

=EDATE(B2,-C2*12)

C2*12是将年龄乘以12返回月数；

月数为负数，则返回之前月数的日期；

出生日期是生日日期之前的日期，所以要在月数前添加负号。