1. 帮助中心 >
  2. 博客 >
  3. 函数用法
返回

【函数】如何根据入职时间计算工龄?

2015-12-24 标签:函数,DATE,YEAR,MONTH,DAY 

这个例子看起来非常简单,比如我是2000年入职,今年2015年,得出的结果应该是15年。可能会有用户认为,用YEAR()函数取两个年份,相减不就好了吗。可是我们要考虑到2015.12.1-2016.5.1这样的情况,只有5个月,不满整年,不能算作工龄。当然年龄也是一样,没有到周岁的时候,是不能算整岁的。因此用年份相减的算法我们予以否定。


方法1:用天数折算成年数

比较相似的是,如果我计算天数呢,算出两个时间的天数差,然后除以365,再取整,不就可以得到年份了吗。我们来做一做这个函数: INT(DAYS(DATE(终止时间),DATE(起始时间))/365)


方法2:用时间戳折算成年数

同理,机智的朋友们也可能直接从时间戳上入手,直接算出两个时间戳的差,然后用毫秒换算取整。一样的,我们来看下:INT((终止时间-起始时间)/1000/60/60/24/365)

 


方法3:精确计算方式

到了这里,细心的朋友会发现,不管用上面的哪一种算法,都不可避免的不精确。365天只是一个年份的大约值,也有可能是366天。虽然影响结果的可能性比较低,但一些要求高的用户还是觉得不好。下面的终极解法,涉及到时间戳本身的定义,比较难理解,这里不要求掌握,有条件的朋友可研究看看。


用到的函数:DATE(),YEAR(),MONTH(),DAY()


是的没错,就用这4个函数,精确的计算整年,精确度为毫秒(时间戳的单位)。这里要清楚一个概念,时间戳的0值,是1970年1月1日0:00:00。 文章的开头笔者说,用年份相减的办法算时间差,我们这里考虑把月份也相减,日期也相减,然后用DATE()把年月日合成时间对象。


假设一个情况,起始时间,2015年1月1日;截止时间,2015年10月1日,这两个相减,得到的应该是DATE(0,10,0)。这里值得一提的是,0日可以看成上个月的最后一天,月份对应减去1即可。那么0年呢?我们知道公元1年和公元前1年之间是没有“0年”这个概念的。跟EXCEL一样,0-99表示1900年-1999年,从100开始,YEAR(DATE(100,1,1))得到的时间值就是公元100年,到后面都不会再有特殊。所以如果前后两个年份相差5年,用YEAR取值得到的应该是1905年,是完全不对的。

    

这里动一下脑子就简单啦。

    

我这样写函数:

    

年份:YEAR(DATE(终止时间))-YEAR(DATE(初始时间))+100

   

月份:MONTH(DATE(终止时间))-MONTH(DATE(初始时间))+1

    

日期:DAY(DATE(终止时间))-DAY(DATE(初始时间))+2

    

这里我想大部分用户朋友都不明白了,年份+100算是理解了,因为终止年份-初始年份如果小于100,得到的值是1900-1999年。这里加上100可以在最后减去。那么为什么月份要+1呢,日期为什么+2呢?

我们设想一下:2015年10月1日-2016年9月30日,这里应该是一年整的。如果按照结尾不加1和2来计算,得到的应该是DATE(101,-1,-1),自动借位,得到的应该是“100年11月30日”,年份-100,最后得到11个月零30天。

==误差是来自哪里?==

    

我们知道一年有12个月,如果两个时间差是0月,实际的时间里不存在0月这个情况,自动向年借1位,年份-1,月份变为12月,天数同理。因此为了避免这样的借位,我们给月份差和天数差各+1,让原本0-11的12进制变为1-12的12进制。最后考虑整年一般是2015年10月1日-2016年9月30日这种,而不是10月1日-次年10月1日。

    

所以最后的天数再加1,这样我们综合公式为:YEAR(DATE(YEAR(DATE(终止时间))-YEAR(DATE(初始时间))+100,MONTH(DATE(终止时间))-MONTH(DATE(初始时间))+1,DAY(DATE(终止时间))-DAY(DATE(初始时间))+2))-100