你好，我是刘卓。欢迎来到我的公号，excel函数解析。昨天分享了eomonth的用法，它是返回某个月份最后一天的日期。今天要分享的函数是edate，它返回指定月数之前或之后的日期。与eomonth有点类似，也是给定一个起始日期，然后返回n个月之前或之后的日期。edate返回一个日期，该日期与起始日期相隔n个月。它有2个参数，语法结构如下所示：
EDATE(start_date, months)第1参数start_date：是一个起始日期，可以是返回日期的任意数据，可以是数字型日期，文本型日期或者函数等。第2参数months：起始日期往前或往后推的月数，可以是正数，0，负数。如果是小数，将截尾取整。注意事项：与eomonth一样，如果第一参数想用数组，需要把区域引用转为数组。下面看几个简单的说明。A列是起始日期，B列是要推的月数，C列是返回的结果。第1个起始日期是2020/4/7，月数是-2，负数就是往前推，2020/4/7往前推2个月就是2020/2/7。2020/12/30往后推3个月就是2020/3/30。这个函数还是比较好理解的。之前我们给一个日期加几个月，用的是date函数。比如下面这样，给2020/4/7加2个月，公式为=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))。但是现在有了edate这个函数就方便多了，公式为=EDATE(A2,B2)。虽然date和edate都能加月数，但是在有些情况中，它们返回的结果还是有些差别的。比如下面这样，给2019/10/31加4个月，date返回的结果是2020/3/2。因为给2019/10/31加4个月，就成了2020/2/31了，而2月没有31号，date会自动转换为2020/3/2。而edate返回的结果是2020/2/29，也就是2月的最后一天。正是利用edate的这个特性，我们可以用它来计算两个日期之间相差的整月数，可以弥补datedif计算整月数的bug，下面会有例子说明。
想要用函数生成以2020年1月开始的连续日期，效果如下图A列所示，就可以用edate函数。公式为=TEXT(EDATE("2020-1-1",ROW(A1)-1),"e年m月")。第1步用edate得到B列的日期，以2010/1/1为起始日期，往后推0,1,2,3...个月；第2步用text取出B列日期的年月。假如男性60岁退休，女性55岁退休，根据出生日期求退休日期。在D32单元格输入公式=EDATE(C32,IF(B32="女",55,60)*12)+1，向下填充。首先用if函数判断出退休的年龄，如果是女的就是55，否则是60。然后把这个年龄加到出生日期的年份上，但是edate只能给月份加，所以把年龄乘以12转为月份，再用edate加。对于这个问题，很多同学肯定是用datedif来计算，公式为=DATEDIF(A39,B39,"m")。但是有些情况下，它算出来的也是不准的。比如起始日期为2019/6/30，结束日期为2020/2/29，它返回的结果为7，实际相差是整8个月。用edate就可以解决这个问题，公式为=LOOKUP(B39,EDATE(A39,ROW($1:$99)-1),ROW($1:$99)-1)。首先把起始日期用edate往后推0,1,2,3,...98个月，这样得到了有99个日期的一个数组。然后在这个99个日期中找到小于等于结束日期的最大值，这个最大值与起始日期相差的月数就是起始日期和结束日期相差的整月数。
这样说你可能不太理解，以起始日期2007/2/20，结束日期2008/3/4为例说明，把起始日期2007/2/20往后推0到98个月，得到了2007/2/20，2007/3/20，2007/4/20，2007/5/20，2007/6/20，2007/7/20，2007/8/20，2007/9/20，2007/10/20，2007/11/20，2007/12/20，2008/1/20，2008/2/20，2008/3/20...等等99个日期。在这99个日期中，小于等于结束日期2008/3/4的最大值是2008/2/20，2008/2/20与起始日期2007/2/20相差12个月。你也可以手动算一下，以方便理解。如果两个日期相差的月数比较多，你可以把99改为999，或者更多。除了用lookup，还可以用match和frequency。match的公式为=MATCH(B39,EDATE(A39,ROW($1:$100)-1))-1，按ctrl+shift+enter三键。frequency的公式为=FREQUENCY(EDATE(A39,ROW($1:$100)),B39)。https://pan.baidu.com/s/1Fufgu9kFCFM8zFE0k34ffg
|
