人事行政文员一定要会的Excel函数公式,学会告别加班!

1.统计部门人数

=COUNTIF(B2:B6,D2)

2.计算迟到早退情况

=IF(B2>TIME(9,0,0),"迟到",")

=IF(C2<TIME(17,0,0),"早退",")

3.身份证判断年龄

=DATEDIF(TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"y")

4.统计员工考核情况

=IF(C2<80,"差",IF(C2<90,"中","优")

5.身份证提取出生日期

=TEXT(MID(B2,7,8),"0000-00-00")

6.计算退休日期

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

7.身份证判断性别

=IF(MOD(MID(B2,17,1),2),"男","女")

8.员工生日提醒

=TEXT(365-DATEDIF(TEXT(MID(B2,7,8),"0000-00-00")TODAY(),"yd”),"还有0天生日;今天生日")

9.计算员工工龄

=DATEDIF(B2,TODAY(),"y")

10.计算合同到期提醒

=D2-TODAY()