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

简道云函数举例:如何根据入职时间计算工龄?

2015-12-24 标签: 

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

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

    很简单,DAYS()函数取两个时间的间隔天数,当然要先用DATE()变成时间对象,然后除以365天,用INT()函数取整即可。看验证:

   

    同理,机智的朋友们也可能直接从时间戳上入手,直接算出两个时间戳的差,然后用毫秒换算取整。一样的,我们来看下:

   

    简单粗暴的公式,结果肯定也不出意料:

 

    到了这里,细心的朋友会发现,不管用上面的哪一种算法,都不可避免的不精确。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 写进公式:  

    我们输入几个日期验算一下:

 

 

区别如下:

 

查看该表单