- A+
在Excel中有一类函数叫做隐藏函数,你无法在Excel的函数列表中找到它们的身影,甚至连微软的帮助文件中都没有相关的说明,但它们不仅 功能强大,而且在工作中应用广泛。
一个DATEDIF是一个神奇的隐藏函数,它存在于Excel中,但是没有在Excel的帮助文件中找到。
现在我们要对这个神奇的隐藏函数进行一次深入的研究。
1
DATEDIF函数详解
DATEDIF的基本语法是:
DATEDIF(开始日期、结束日期、间隔类型)
其中,参数start_date和end_date是两个日期,前者不得超过后者。
unit有以下6个参数,用于计算不同的差异,如下表所示。
![你的Excel有DATEDIF功能吗?-1 你的Excel有DATEDIF功能吗?-1](http://she.d1qu.com/wp-content/themes/begin/img/blank.gif)
在日常拼写中,有些人会错过函数名称中间的D,变成DATEIF,这是错误的,当输入错误时,Excel系统不会提示。
这个函数词有一个简单的记忆方法:DATEDIF在DateDifferent中缩写,翻译成不同的日期。
2
计算两个日期间的年、月、日间隔
如下图所示,这是DATEDIF的常用方法,这6个参数的实际意义可以结合图中的数据进行说明。
DATEDIF常规用法
首先,在D16、D24单元格中分别输入以下公式,向下复制到D21、D29单元格,计算不同参数的不同
=DATEDIF(E16、F16、C16)
=DATEDIF(E24、F24、C24)
D24单元格,参数Y,单看2017年和2020年,相差年限应该是3,但是从2017/7/28到2020/2/8,先是2年到2019/7/28,还没有达到要求的2020/2/8,再过一年,就到了2020/7/28,超过了结束日期,所以结果回到2,不能回到3。
D25单元格,参数m,2017/7/28超过30个月达到2020/1/28,再过1个月达到2020/2/28,超过结束日期2020/2/8,结果只有30,不能达到31。要充分体会整年数整月数中整字的含义。
D26单元格、参数d相当于两个日期直接减少,计算天数的差异。
D27单元格,参数MD,这个计算无视月和年,相当于将start_date拉近end_date前最接近的日期。也就是说,将2017/7/28拉近到2020/2/8的最接近日,即2020/1/28,计算2020/1/28和2020/2/8之间的天数差,即11天。
D28单元格,参数YM,无视日期和年度计算月数,即将2017/7/28接近2020/2/8的最接近7月28日,成为2019/7/28,计算与2020/2/8之间的整月数之差,即6个月。
D29单元格,参数YD,无视年计算天数差,相当于将start_date拉近end_date前最接近的同一个月和同一天的日期。也就是说,将2017/7/28拉近2019/7/28,计算2019/7/28和2020/2/8之间的天数差,即195天。
在使用MDYD参数计算天数较差时,由于闰年的存在,有时与理想值相差一天,这种情况一般不会影响我们的日常使用。
3
整年、月、日区别
如下图所示,列出了2017/7/28到2020/7/27与2017/7/28到2020/7/28的对比,虽然end_date只差了1天,但是结果有比较大的差异。计算原理相同,要体会“整”字的含义。
整年、月、日区别
这么多参数需要怎么记忆?首先要了解这个函数的作用,了解每个参数的计算原理。工作中经常需要计算日期的话,可以印刷,贴在桌子旁边查一下。
4
案例:工龄计算
假设今天是2019/7/28,每个员工参加工作的日期如下图c列所示,每个人的工作年数是多少?工作年数可以表示为m年n个月的形式。
![你的Excel有DATEDIF功能吗?-4 你的Excel有DATEDIF功能吗?-4](http://she.d1qu.com/wp-content/themes/begin/img/blank.gif)
可以分阶段 操作。先计算整年数,再计算整月数。计算月数时,请注意月数值最大不超过11。因为到了12个月就是1年了,所以无视年份的存在计算月数。那么用哪些参数来计算呢?
从上一节所说的DATEDIF参数对照表可以看出,计算全年使用参数Y,而忽略了年计算全月使用YM。D51单元格的函数公式可以写成:
(左右拖动看完整的公式)
=DATEDIF(C51、“2019/7/28”、“Y””&“年”&DATEDIF(C51、“2019/7/28”、“YM”&“月”
让我们看看D54:D56单元格区域。只差一天,计算结果就不同了。因此,使用DATEDIF时,必须有整的概念。
另外,DATEDIF中的Y、M、D参数、大小写都可以。
5
案例:年假天数计算
根据《员工带薪年假条例》,员工累计工作已满1年未满10年的,年假为5天;已满10年未满20年的,年假为10天;已满20年的,年假为15天。
再重申一遍,假设今天是2019/7/28,每个雇员的年休假分别是多少天?
实际上,这一主题比上节中的案例要简单得多,只要知道每个员工参与工作的年数就可以了。
如下图所示,在D66单元格中输入下面的公式来计算每个雇员的工作年数:
*DATEDIF(C66,DATE(2019,7,28)"Y")
![你的Excel有DATEDIF功能吗?-5 你的Excel有DATEDIF功能吗?](http://she.d1qu.com/wp-content/themes/begin/img/blank.gif)
此处再次强调,如果在公式中使用快速输入的方式来表达日期,则必须添加双引号,例如上节中的“DATEDIF(C51,2019/7/28,Y)”,如果不能掌握双引号的使用,则必须定期使用DATE函数,以确保不会出错。
按D列年数计算法定年假天数,在E66单元格中输入下列公式:
=LOOKUP(D66,{0,1,10,20},{0,5,10,15})
历史上的今天:
- 2021: 系统文档目录迁移修改工具V1.2
- 2021: DLL修复工具DirectX 4.0 正式版
- 2020: 短视频必备的特效视频片头大师APP(VIP版)