点上方关注我们,每天早上5分钟,工作多点轻松
朋友们好,我是星光,今天咱们来继续学习Excel函数,聊一下日期计算必会函数DATEDIF。
DATEDIF是Excel的隐藏函数。只所以说它是隐藏函数,是因为咱们在工作表中输入这个函数看不到任何提示信息,函数列表里没有它,帮助文件里也没有它,就好像这家伙压根不存在一样。但这家伙不但真实存在,还很实用,在工作中的应用也非常广泛,常用于计算两个日期之间的天数、月数或年数。
该函数基本语法如下:
DATEDIF(开始日期,结束日期,计算类型)
重点说一下第3参数。它代表了日期信息返回的型,不区分大小写,不同的参数下表所示。 举几个常用的案例。 1,两日期之间相差的天数
=DATEDIF(A2,B2,"d")
这个倒不常用,我们更常用:
=B2-A2
2,两日期之间相差的整月数
=DATEDIF(A2,B2,"m")
3,两日期之间相差的整年数
=DATEDIF(A2,B2,"y")
有朋友想,是不是也可以使用公式:
=YEAR(B2)-YEAR(A2)
答案是——不可以。
这里说的相差年数,是指整年。2016年11月11日只有到了2020年11月11日之后,才算相差4年是不是?而公式=YEAR(B2)-YEAR(A2)显然没有考虑到这一点。
所以DATEDIF这个套路常用于计算一个人的年龄。比如假设A2为出生日期,则这个人的年龄为:
=DATEDIF(A2,TODAY(),"y")
4,忽略月和年,计算两日期相差的天数
=DATEDIF(A2,B2,"md")
也可以使用公式:
=DAY(B2)-DAY(A2)
5,忽略日和年,计算两日期相差月数
=DATEDIF(A2,B2,"ym")
6,忽略年,计算两日期相差天数
=DATEDIF(A2,B2,"yd")
基础用法里最实用最常用的就是第3条了,请务必掌握。接下来咱们再看下一个比较经典的案例:计算工龄
取支烟,点火,来条广告,稍后回来。
如下图所示。A列是人名,B列是入职时间,C列是截止日期,计算每个人的工龄。 1,D列按整年算工龄,公式想必你已经知道了。
=DATEDIF(B2,C2,"y")&"年"
2,E列将工龄精确到月份,也就是几年几个月。几个月需要忽视掉年,咱们可以使用公式:
=DATEDIF(B2,C2,"ym")&"月"
将年和月合并到一起就是结果了。
=DATEDIF(B2,C2,"y")&"年"& DATEDIF(B2,C2,"ym")&"月"
3,F列将工龄精确到天,也就是几年几月几天。几天需要忽视掉年和月,咱们可以用公式:
=DATEDIF(B2,C2,"md")&"天"
将年月天合并到一起就是最终结果了。
=DATEDIF(B2,C2,"y")&"年"&DATEDIF(B2,C2,"ym")&"月"& DATEDIF(B2,C2,"md")&"天
但这样价值观是不是太朴素了?咱能不能有点追求?有点梦想?娶个女神嫁个富三代?不是,咱能不能把公式精简下,高级点? 可以试下以下数组公式:
=TEXT(SUM(DATEDIF(B2,C2,{"y","ym","md"})*{10000,100,1}),"00年00月00日")
公式解析:
DATEDIF(B2,C2,{"y","ym","md"}),这部分计算两个日期的年、月、日差值,结果是一个内存数组:{13,11,7}
然后用这个内存数组乘以{10000,100,1},也就是{13,11,7}*{10000,100,1}),其中13*10000,11*100,7*1。这一步的意义是对不同的值进行加权,以两位数进行间隔,分隔到不同位置。
最后使用SUM函数对乘积结果求和,得到131107。其中前两位是年。中间两位是月。最后两位是日。
最外围的TEXT函数第3参数为:"00年00月00日",也就是对SUM函数返回的结果每两位分一个主子,前两位分给年,中间两位分给月,最后两位分给日……
是不是很有意思? 最后呢,再给大家讲一个小秘密。
有女朋友说了,DATEDIF的第3参数也太乱了。一会YM,一会MD,一会又YD的,这谁整的明白啊?
打个响指,听我说。最后一个字母是要计算的目标,第1个字母是计算目标的忽视范围内的上一级。
比如YM,目标是整月,忽视日和年。M是month的首字母,也就是月的意思,月的上一级是年year,Y是year的首字母。
比如MD,目标是天,忽视月和年。D是day的首字母,也就是日的意思,日的上一级是月,也就是M。
男朋友就说了,瞎扯,YD就不符合这规则——其实是符合的。YD的意思是忽视年,计算天数。计算目标是D。D的上一级应该是月,但是,由于月并不在忽视的范围内,所以D的上一级当然是Y……
信不信由你,反正我信了。
……
…… 友情提示,DATEDIF是一个天生 携带BUG 的函数,在计算日期临近月底最后一天时,可能会导致计算出错。通常这是可以接受的,但如果需要特别严格的数据,这当然也是不可以接受的。请视情况选择是否使用。
关于bug的讨论,参考论坛经典帖子:http://club.excelhome.net/thread-463826-1-1.html
示例文件下载:
https://pan.baidu.com/s/1moWK1c7RB-dp6Yp6eDHB0A
提取码: fkvq 系统学习Excel,推荐加入我的Excel社群 ▼
更多教程&练习
教程1:零基础学Excel(一)什么是Excel?
教程2:零基础学SQL in Excel 25篇合集
作业集:学习不练习=没学习,答题开始!
©看见星光
点赞!