你好，我是劉卓。歡迎來到我的公號(hào)，excel函數(shù)解析。昨天分享了eomonth的用法，它是返回某個(gè)月份最后一天的日期。今天要分享的函數(shù)是edate，它返回指定月數(shù)之前或之后的日期。與eomonth有點(diǎn)類似，也是給定一個(gè)起始日期，然后返回n個(gè)月之前或之后的日期。edate返回一個(gè)日期，該日期與起始日期相隔n個(gè)月。它有2個(gè)參數(shù)，語法結(jié)構(gòu)如下所示：
EDATE(start_date, months)第1參數(shù)start_date：是一個(gè)起始日期，可以是返回日期的任意數(shù)據(jù)，可以是數(shù)字型日期，文本型日期或者函數(shù)等。第2參數(shù)months：起始日期往前或往后推的月數(shù)，可以是正數(shù)，0，負(fù)數(shù)。如果是小數(shù)，將截尾取整。注意事項(xiàng)：與eomonth一樣，如果第一參數(shù)想用數(shù)組，需要把區(qū)域引用轉(zhuǎn)為數(shù)組。下面看幾個(gè)簡單的說明。A列是起始日期，B列是要推的月數(shù)，C列是返回的結(jié)果。第1個(gè)起始日期是2020/4/7，月數(shù)是-2，負(fù)數(shù)就是往前推，2020/4/7往前推2個(gè)月就是2020/2/7。2020/12/30往后推3個(gè)月就是2020/3/30。這個(gè)函數(shù)還是比較好理解的。之前我們給一個(gè)日期加幾個(gè)月，用的是date函數(shù)。比如下面這樣，給2020/4/7加2個(gè)月，公式為=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))。但是現(xiàn)在有了edate這個(gè)函數(shù)就方便多了，公式為=EDATE(A2,B2)。雖然date和edate都能加月數(shù)，但是在有些情況中，它們返回的結(jié)果還是有些差別的。比如下面這樣，給2019/10/31加4個(gè)月，date返回的結(jié)果是2020/3/2。因?yàn)榻o2019/10/31加4個(gè)月，就成了2020/2/31了，而2月沒有31號(hào)，date會(huì)自動(dòng)轉(zhuǎn)換為2020/3/2。而edate返回的結(jié)果是2020/2/29，也就是2月的最后一天。正是利用edate的這個(gè)特性，我們可以用它來計(jì)算兩個(gè)日期之間相差的整月數(shù)，可以彌補(bǔ)datedif計(jì)算整月數(shù)的bug，下面會(huì)有例子說明。
想要用函數(shù)生成以2020年1月開始的連續(xù)日期，效果如下圖A列所示，就可以用edate函數(shù)。公式為=TEXT(EDATE("2020-1-1",ROW(A1)-1),"e年m月")。第1步用edate得到B列的日期，以2010/1/1為起始日期，往后推0,1,2,3...個(gè)月；第2步用text取出B列日期的年月。假如男性60歲退休，女性55歲退休，根據(jù)出生日期求退休日期。在D32單元格輸入公式=EDATE(C32,IF(B32="女",55,60)*12)+1，向下填充。首先用if函數(shù)判斷出退休的年齡，如果是女的就是55，否則是60。然后把這個(gè)年齡加到出生日期的年份上，但是edate只能給月份加，所以把年齡乘以12轉(zhuǎn)為月份，再用edate加。3.計(jì)算兩個(gè)日期之間相差的整月數(shù)對(duì)于這個(gè)問題，很多同學(xué)肯定是用datedif來計(jì)算，公式為=DATEDIF(A39,B39,"m")。但是有些情況下，它算出來的也是不準(zhǔn)的。比如起始日期為2019/6/30，結(jié)束日期為2020/2/29，它返回的結(jié)果為7，實(shí)際相差是整8個(gè)月。用edate就可以解決這個(gè)問題，公式為=LOOKUP(B39,EDATE(A39,ROW($1:$99)-1),ROW($1:$99)-1)。首先把起始日期用edate往后推0,1,2,3,...98個(gè)月，這樣得到了有99個(gè)日期的一個(gè)數(shù)組。然后在這個(gè)99個(gè)日期中找到小于等于結(jié)束日期的最大值，這個(gè)最大值與起始日期相差的月數(shù)就是起始日期和結(jié)束日期相差的整月數(shù)。
這樣說你可能不太理解，以起始日期2007/2/20，結(jié)束日期2008/3/4為例說明，把起始日期2007/2/20往后推0到98個(gè)月，得到了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個(gè)日期。在這99個(gè)日期中，小于等于結(jié)束日期2008/3/4的最大值是2008/2/20，2008/2/20與起始日期2007/2/20相差12個(gè)月。你也可以手動(dòng)算一下，以方便理解。如果兩個(gè)日期相差的月數(shù)比較多，你可以把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
|
