|
编按：哈喽，大家好！今天是部落窝函数课堂第4课，我们将认识DATEDIF函数！DATEDIF函数不仅可以用来计算年龄、工龄、工龄工资、项目周期，还可以用来做生日倒计时提醒，项目竣工日倒计时提醒等等。用上它，您再也不会缺席那些重要的日子，不论是亲人生日、项目竣工日，还是儿女的毕业典礼日。 ———————————————————— DATEDIF函数和我们平时见到的函数有所不同。大家都知道，一般我们只要在EXCEL中输入函数字母的前几位，EXCEL就会自动弹出该函数，然而这个函数字母都输完了，EXCEL仍没有任何提示。有的小伙伴可能都会怀疑是否有这个函数。其实DATEDIF函数是EXCEL隐藏函数，在帮助和插入公式里面是没有的，只能纯手工输入。 DATEDIF函数不仅可以用来计算年龄、工龄、工龄工资、项目周期，还可以用来做生日倒计时提醒，项目竣工日倒计时提醒等等。下面我们就来认识认识它。 一、 初识DATEDIF DATEDIF函数用于计算两日期之差，返回两个日期之间的年、月、日间隔数 函数结构：DATEDIF（起始日期,结束日期,返回类型） 1.参数解释： 1）起始日期和结束日期 起始日期、结束日期作为需要计算差异的两个日期。 这两个日期的输入方法如下： 可以直接输入带引号的日期，例如"2017/10/16"。注意起始日期不能早于1900年，结束日期要大于起始日期。 也可以直接引用单元格中的日期 还可以利用其他函数得到，例如TODAY() （注意：范例当日是2019年2月15日） 2）返回类型 返回类型用于设置结算结果的类型。返回类型是文本，输入时须要带双引号。 y：返回两个日期之间相差整年数（不足一年的不计） m：返回两个日期之间相差整月数（不足一月的不计） d：返回两个日期之间相差的天数 ym：计算两日期之间略去整年差异后的整月数差异。譬如，两个日期（2017-4-20,2019-2-20）相差1年10月，略去整年差异1年，则ym的结果就是10月。再譬如，两个日期（2018-4-20,2019-2-20）相差10月，则ym的结果是10月。 yd：计算两日期之间略去整年差异后的天数差异。譬如，两个日期（2017-4-20,2019-2-20）相差1年零306天，略去整年差异1年，则ym的结果就是306天。 md：计算两日期之间略去整年和整月差异后的天数差异。譬如，两个日期（2017-4-20,2019-2-25）相差1年10月零5天，略去整年和整月差异1年10月，则md的结果就是5天。 2.举个栗子 DATEDIF("2017/2/15","2019/2/15","y")，计算"2017/2/15"与"2019/2/15"之间相差几个整年。这里相差两个完整的年，所以等于2。 DATEDIF("2017/1/6","2019/2/15","d")，计算"2017/1/6"与"2019/2/15"之间相差的天数，等于770。 DATEDIF("2017/1/6","2019/2/15","ym")，计算两日期之间除开整年外的间隔月数。两日期之间实际相差25月，包含了2个整年（24月），所以ym类型返回值为25-24=1。 DATEDIF("2017/1/6","2019/2/15","yd")，计算两日期之间除开整年外的间隔天数。两日期之间实际相差770天，包含了2个整年（730天），所以yd类型返回值为770-730=40。 3.使用要点 1）双引号 到这里，相信小伙伴们对于DATEDIF函数已经有了初步的认识，可以写几个公式练练手啦。写公式中需注意双引号的使用。 （1）如果第1、2参数是直接输入日期，则日期必须带双引号。 （2）第3参数是文本，一定要记得带上双引号。 2）错误类型 DATEDIF函数如果发生错误，通常有以下三类： 二、DATEDIF函数实际应用例举 1.根据出生日期计算年龄 已知下面员工的出生日期，求他们今年的年龄。
公式：=DATEDIF(D2,TODAY(),"y")
TODAY()函数获取的是系统当前日期，列举的实例为2019/2/15日的计算结果，并不一定和小伙伴们得到的结果相符哦~ 怎么样？是不是很简单呢？ 2.根据身份证号码计算年龄 上一例中已经有了出生日期，所以直接用DATEDIF函数套用TODAY函数即可计算出年龄。如果只有身份证号码，要计算年龄，就需要把出生日期从身份证号码中提取出来后再计算。公式如下：
公式解析： 使用MID函数提取出身份证号码中出生日期的8位数字。
用TEXT函数让这8位数字以"0-00-00"的格式显示，得到像日期格式的文本，然后在TEXT函数前加上负负得正的运算，将文本转换为日期。
最后将上面得到的日期作为DATEDIF函数的起始日期，将TODAY()作为结束日期，设置返回类型为“y”,即可计算出两日期之间相差的整年数——年龄。 3.根据入职日期计算员工工龄（以年月日的形式展现） 用例1计算年龄的方法，如果知道员工入职的时间，即可计算出按整年计的员工工龄。但如果需要计算出详细的员工工龄，如多少年多少月多少天，该怎么做呢？答案如下：
公式虽长，却特别好理解。首先用三个DATEDIF函数分别计算出两日期之间相差几年几月几日，最后再用文本连接符“&”进行连接，得到结果。 4.计算工龄工资 根据2019年国家出台的工龄工资规定，员工连续工作满一年 50元/月；连续工作满两年 100元/月；连续工作满三年 150元/月；连续工作满四年180元/月，以此类推，累计十年封顶。 小伙伴是不是一头雾水呢？没事，我们一步一步来，首先计算工龄（按整年计算）。 公式：=DATEDIF(C2,D2,"y")
接着，来到我们的重头戏，计算工龄工资。 公式：=IF(E2 这里我们借助了IF函数和MIN函数。 根据2019年国家出台的工龄工资规定，1-3年工龄工资每年是以50来递增的，4-10年的工龄工资每年是以30来递增的。我们可以使用IF函数分开判断。 首先判断工龄E2是否小于4，小于4则表示员工工龄工资是以每年50来递增，返回“50*E2”的结果；如果工龄E2不小于4，工龄工资则是在150的基础上以每年30来递增，返回“150+30*(MIN(10，E2)-3)”的结果。 因为工龄工资只能累计十年，大于十年的工龄工资与十年的工龄工资一致，所有我们使用MIN函数返回10和E2中的最小值作为工龄。 5.制作员工生日提醒 下面是一张员工的信息表，我们想做一个生日提醒，提前7天提醒某员工的生日快到了。
提示：和IF函数结合使用，快开动脑筋想一想吧~ 公式：=IF(DATEIF(D3-7,TODAY(),"yd")
是不是感觉这个公式很烧脑？ 我们日常计算距离生日的天数都是用即将到来的生日日期减去今天的日期。而这个公式与我们的习惯不同，它用今天的日期减去出生日期进行计算，并且还将出生日期减少了7天。 为何能这样做？ 首先我们来看看yd返回类型下不同的当前日期与出生日期的间隔天数规律。下表以出生日期1999年2月22日为例，展示了昨天、今天、明天、后天等距离出生日期的天数。
N16单元格公式= DATEDIF($J$13,N15,"yd")，$J$13代表出生日期，N15代表不同的当前日期。 很明显，生日当天间隔为0；小于生日日期的，日期越趋近生日，间隔天数越大越趋近365；大于生日日期的，日期越趋近生日，间隔天数越小越趋近0。 其次，在这种情况下，直接套用IF函数根据间隔天数是否小于等于7来给出生日提醒的公式=IF(DATEDIF($J$13,N15,"yd")
最后，那怎么才能提前7天提醒？有两种方法。第一种，设法让间隔天数0-7提前7天出现。这时，要么把起始日期减少7天（$J$13-7），要么把结束日期增加7天（N15+7），如下： 起始日期减少7天后的间隔天数
起始日期减少7天后的生日提醒
第二种，修改判断条件，把=358即可。如下：
修改判断条件后，生日当天不会提醒。 Ok，到这里，相信大家就理解前面的公式了。在此基础上，我们可以修改公式，让提醒更人性化： =IF(DATEDIF(D3-7,TODAY(),"yd")
再多说两句：如果按平常思路用即将到来的生日日期减去当前日期来计算距离生日的天数，生日提醒公式该怎么写呢？答案如下： =IF(DATEDIF(TODAY(),IF(TEXT(D3,"M月DD日")月DD日"),YEAR(TODAY()+365),YEAR(TODAY()))&"年"&TEXT(D3,"M月DD日"),"yd")
这是一个非常长的公式！！！ 长就长在即将到来的生日日期提取。 公式中的IF(TEXT(D3,"M月DD日")月DD日"),YEAR(TODAY()+365),YEAR(TODAY()))&"年"&TEXT(D3,"M月DD日")用于获取即将到来的生日日期。意思是：如果出生日期中的月日数小于今日的月日数，说明今年的生日已经过去了，新的生日日期应该是YEAR(TODAY()+365)&"年"&TEXT(D3,"M月DD日"；反之，说明今年的生日还没过，生日日期应该是YEAR(TODAY())&"年"&TEXT(D3,"M月DD日"。 YEAR(TODAY())提取今年的年份，加上365，则得到明年的年份。 TEXT(D3,"m月dd日")提取出生日期中的月份和号数。 到此，DATEDIF函数就介绍完毕。不论是计算年龄、工龄、工龄工资，还是给出生日提醒，都可以用DATEDIF实现。当然，DATEDIF也完全可以用来计算项目用时、距离完工日天数，做完工倒计时提醒。如果你是做人事、做工资核算、做项目管理的，那么赶紧操练起来吧！ ＊＊＊＊部落窝教育－excel函数DATEDIF技巧＊＊＊＊ 原创：壹仟伍佰万/部落窝教育（未经同意，请勿转载） |
|
|
来自： 部落窝教育BLW > 《部落窝excel/VBA》
