你的Excel有DATEDIF功能吗？

  • A+
所属分类：百科知识

在Excel中有一类函数叫做隐藏函数，你无法在Excel的函数列表中找到它们的身影，甚至连微软的帮助文件中都没有相关的说明，但它们不仅 功能强大，而且在工作中应用广泛。

一个DATEDIF是一个神奇的隐藏函数，它存在于Excel中，但是没有在Excel的帮助文件中找到。

现在我们要对这个神奇的隐藏函数进行一次深入的研究。

1
DATEDIF函数详解
DATEDIF的基本语法是：
DATEDIF(开始日期、结束日期、间隔类型)
其中，参数start_date和end_date是两个日期，前者不得超过后者。
unit有以下6个参数，用于计算不同的差异，如下表所示。

你的Excel有DATEDIF功能吗？-1

在日常拼写中，有些人会错过函数名称中间的D，变成DATEIF，这是错误的，当输入错误时，Excel系统不会提示。

这个函数词有一个简单的记忆方法:DATEDIF在DateDifferent中缩写，翻译成不同的日期。
2
计算两个日期间的年、月、日间隔
如下图所示，这是DATEDIF的常用方法，这6个参数的实际意义可以结合图中的数据进行说明。

你的Excel有DATEDIF功能吗？-2

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天，但是结果有比较大的差异。计算原理相同，要体会“整”字的含义。

你的Excel有DATEDIF功能吗？-3

整年、月、日区别

这么多参数需要怎么记忆？首先要了解这个函数的作用，了解每个参数的计算原理。工作中经常需要计算日期的话，可以印刷，贴在桌子旁边查一下。
4
案例：工龄计算
假设今天是2019/7/28，每个员工参加工作的日期如下图c列所示，每个人的工作年数是多少？工作年数可以表示为m年n个月的形式。

你的Excel有DATEDIF功能吗？-4

可以分阶段 操作。先计算整年数，再计算整月数。计算月数时，请注意月数值最大不超过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功能吗？

此处再次强调，如果在公式中使用快速输入的方式来表达日期，则必须添加双引号，例如上节中的“DATEDIF(C51,2019/7/28,Y)”，如果不能掌握双引号的使用，则必须定期使用DATE函数，以确保不会出错。
按D列年数计算法定年假天数，在E66单元格中输入下列公式：
=LOOKUP(D66,{0,1,10,20},{0,5,10,15})

历史上的今天:

发表评论

您必须登录才能发表评论！