亲们,今天我们来说说要如何计算员工的工龄、年龄。
所需要用到的,是Excel中的一个神秘函数。之所以说它神秘,是因为它是Excel中的一个隐藏函数:DATEDIF函数。也可能会有不少朋友用过这个函数,但是,您知道这个函数有一个大BUG么?使用这个函数如果不注意避开其中的“大坑”,可能会导致计算结果不准哦~~
一、DATEDIF函数初探
1、功效:DATEDIF函数:用于计算两个日期之间的天数、月数或年数。
2、用法:DATEDIF( 开始日期,结束日期,控制开关)
关于参数中的“控制开关”,是酱柿儿滴:
Y:年数(即,从开始日期到结束日期,一共有几年)
M:月数(即,从开始日期到结束日期,一共有几个月)
D:天数(即,从开始日期到结束日期,一共有几天)
3、用法举例
比如说,要计算工龄(或年龄)有几个月,公式是这样的:
=DATEDIF("2016-12-1","2017-4-17","m")
即:计算2016年12月1日至2017年4月17日有多少个完整的月份。
如果,想要计算工龄(或年龄)有多少年,公式是这样的:
=DATEDIF("2008-6-1","2017-4-17","y")
即:计算2008年6月1日到2017年4月17日有多少个整年数。
具体应用见下表举例:
注:上面设置了一个放置截止日期的单元格D2。为了确保第一个公式做好后,能够下拉复制公式,因此在公式中对D2单元格的引用进行了锁定(即变成了D$2),相信经常使用公式的HR亲,都会常常用到Excel单元格的相对和绝对引用。
二、DATEDIF函数的坑坑坑,你要进来吗?
这个函数非常好用!但是! 这个函数也有一个大Bug,稍不注意可能会出现计算错误!
大家有没有注意到,上面计算公式的举例中,“员工1”计算的工龄月数,是错误的!——就是下面这个图中标出的位置。(公式仍然是上面的例子中的公式)
各位亲,看得出错误吗?
员工1,入职日期2017年3月31日,工龄计算的计算日期是2017年4月30日。
4月份只有30天,因此4月的最后一个日期即是4月30日。而3月份最后1天是3月31日。
从这两个日期来看,员工1的入职月数应该是1个月,而不是上图计算出来的“0”!
这就是我们所说的Bug!
为什么会出现这个问题呢?
原来:只有当DATEDIF的结束日期是当月的最后一天,而开始日期的天数比结束日期的天数大的时候,计算结果会少一个月。
既然找到了这只大Bug,那我们怎么解决呐?
我们把公式升级改造下~~~
将公式修改为:
=DATEDIF(C4,D$2,"m")+AND(DAY(C4)>DAY(D$2),D$2=EOMONTH(D$2,0))
改造后的公式,就完全没问题啦!
---------------------敲黑板、划重点---------------------
上面的公式,看上去很长,其实就是两个简单函数所组成的,不要怕!
下面我们来讲一下上面公式的思路(看不懂的亲,先拿公式去套用,慢慢领会):
思路:
针对前面BUG的情况增加一个判断:当开始日期的天数大于结束日期的天数,并且结束日期是否当月的最后一天时,就在DATEDIF公式结果上加1个月,否则就保持DATEDIF的结果。
DAY(开始日期C4)>DAY(截止日期D$2):是判断开始日期“年月日”的中的“日”,是否大于结束日期“年月日”的的“日”。
EOMONTH(D$2,0):输出当前日期(即公式中的D2)所在月份的最后一天。如果D2日期是2017年4月17日,则这个函数的结果为2017年4月最后一天:2017年4月30日。
D$2=EOMONTH(D$2,0):判断结束日期是否当月的最后一天。
而And函数是一个逻辑判断函数,只有当判断的条件都满足时,其输出结果为1(即为“真”,TRUE),不满足条件时输出为0(即为假,FALSE)。
也就是说,前面的条件,都满足,就在原来DATEDIF算出的月份上+1,条件不满足则无需+1。
说了这么多,是不是说清楚了呢?
希望获得更多应用技巧,可以关注我后续的分享~~~,也可以加我的公众号或微信(具体在页面右侧我的个人简介中),有更多的HR朋友一起交流分享,不失为一件乐事。