編按：

日常工作中，我們必然會遇到各種日期問題，需要對它進行查找、計算……今天，小E給大家介紹的就是計算有效期天數(shù)的方法！從簡單到較難的三個不同問題，全面為大家解構(gòu)公式的組成和應(yīng)用！

在EXCEL中，日期的本質(zhì)是數(shù)值，所以一般情況下，在計算有效期的時候，只要適當(dāng)?shù)剡\用日期函數(shù)，基本上都能夠得出預(yù)期的結(jié)果。可是，當(dāng)遇到某些奇葩的有效期計算要求的時候，就要多殺死一些腦細(xì)胞了。

小張就職于一家寵物食品公司，對寵物食品的保質(zhì)期有嚴(yán)格的要求。小張的日常工作之一就是對保質(zhì)期進行計算和管理。

最簡單的日期計算

原本呢，公司生產(chǎn)的寵物食品的保質(zhì)期，規(guī)定為70天。所以，要計算70天有效期的話，只需將生產(chǎn)日期加上70就可以了，這對于小張來說，是個小case。

操作：在B2中輸入“=A2+70”，用鼠標(biāo)向下復(fù)制填充即可。

不難的日期計算

沒過多久，公司出了新的規(guī)定，規(guī)定保質(zhì)期為2個月零10天。這種情況下的有效期計算也在小張的射程范圍之內(nèi)——先用EDATE函數(shù)求得兩個月之后的日期，然后再加10，就行了。

操作：在B2中輸入“=EDATE(A2,2)+10”，用鼠標(biāo)向下復(fù)制填充即可。

難度升級的日期計算

近日，公司又出了更奇葩的規(guī)定——將保質(zhì)期定為2個月零10天的同時，要求有效期的最后一位數(shù)字與生產(chǎn)日期最后一位數(shù)字保持一致！這下，情況就變得復(fù)雜了。

問題分析：

這里面會涉及到每月天數(shù)、閏年和平年2月份天數(shù)不同的問題，甚至涉及到平年跨到平年、平年跨到閏年等多種狀況。以2019年12月20日為例，向后推移2個月，應(yīng)為2020年2月20日，2020年2月20日之后的第一個尾數(shù)為0的日期是2020年3月10日。

在公司的這種規(guī)定下，天數(shù)差就不再是一個有明確規(guī)律的數(shù)字了，最低天數(shù)差值為69天，而最高的天數(shù)差值則高達(dá)81天：

小張一籌莫展，這真的是難死“寶寶”了。

思路：

通過上面對于天數(shù)差值的分析，大家可以看出，有效期與生產(chǎn)日期之間的天數(shù)差值范圍，在69天至81天之間。先將生產(chǎn)日期往后推69天至81天的天數(shù)構(gòu)成一個數(shù)組，然后再在這個數(shù)組中，把第一個與生產(chǎn)日期天數(shù)的最后一位數(shù)字一致的天數(shù)匹配出來。用數(shù)組來解決這個問題。

操作：

在B2中的輸入：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

這樣，就完美的計算出了對應(yīng)的有效期！

函數(shù)分析：

INDEX+MATCH函數(shù)是Excel中的經(jīng)典組合，先用MATCH函數(shù)匹配到符合條件的數(shù)據(jù)的位置，再通過INDEX函數(shù)返回數(shù)組（或區(qū)域）中這個位置對應(yīng)的值。

公式看起來很長，其實理解起來很簡單！下面，筆者就為大家一一剖析構(gòu)成這個公式的步驟：

第1步：構(gòu)造日期數(shù)組

在B2中輸入公式如下：=A2+ROW($69:$81) ，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計算結(jié)果，如下：

={44286;44287;44288;44289;44290;44291;44292;44293;44294;44295;44296;44297;44298}

其中顯示的是日期對應(yīng)的數(shù)值，不太容易看清楚效果。

補充檢測小技巧：

大家可以增加一列輔助列，利用TEXT函數(shù)將數(shù)值改成日期顯示。

在C2中輸入公式：=TEXT(A2+ROW($69:$81),"yyyy-m-d")，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看C2中的計算結(jié)果，如下：

={"2021-3-31";"2021-4-1";"2021-4-2";"2021-4-3";"2021-4-4";"2021-4-5";"2021-4-6";"2021-4-7";"2021-4-8";"2021-4-9";"2021-4-10";"2021-4-11";"2021-4-12"}

【注：TEXT函數(shù)在后續(xù)的計算中，用不到它?！?/span>

可以看到，在構(gòu)造出的數(shù)組中，天數(shù)尾數(shù)為1的有3個，分別為2021年3月31日、2021年4月1日、2021年4月11日，這些天數(shù)的尾數(shù)都與2021年1月21日的天數(shù)尾數(shù)一致。

第2步：構(gòu)建天數(shù)數(shù)組

通過DAY函數(shù)將構(gòu)造出的日期數(shù)組中的每一個日期的天數(shù)提取出來，構(gòu)成一個新的數(shù)組。

B2中的公式擴展為：=DAY(A2+ROW($69:$81))，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計算結(jié)果，如下：

={31;1;2;3;4;5;6;7;8;9;10;11;12}

第3步：構(gòu)建天數(shù)差值數(shù)組

將天數(shù)數(shù)組中的天數(shù)與生產(chǎn)日期中的天數(shù)相減，以求得天數(shù)差值。

B2中的函數(shù)第二次擴展為：=DAY(A2+ROW($69:$81))-DAY(A2)，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計算結(jié)果，如下：

={10;-20;-19;-18;-17;-16;-15;-14;-13;-12;-11;-10;-9}

肉眼觀察即知，與2021年1月21日的日期尾數(shù)相同的天數(shù)與2021年1月21日的天數(shù)差為10（2021年3月31日），-20（2021年4月1日），-10（2021年4月11日），在天數(shù)差值數(shù)組中，第一個天數(shù)差值為10的整數(shù)倍的數(shù)據(jù)，就對應(yīng)著日期數(shù)組中的第一個滿足條件的日期。

第4步：構(gòu)建余數(shù)數(shù)組

思路：

由于滿足條件的天數(shù)差值為10，-20，20，它們的一致規(guī)律就是除以10后求余數(shù)，均為0。從而，我們可以得出這樣結(jié)論：第一個為余數(shù)為0的數(shù)據(jù)，也對應(yīng)著日期數(shù)組中的第一個滿足條件的日期。

B2中的函數(shù)修改為：=MOD(DAY(A2+ROW($69:$81))-DAY(A2),10)，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計算結(jié)果，如下：

={0;0;1;2;3;4;5;6;7;8;9;0;1}

第5步：通過MATCH函數(shù)，進行匹配

通過MATCH函數(shù)，在余數(shù)數(shù)組中，匹配第一次出現(xiàn)0的位置。

B2中的函數(shù)修改為：=MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0)，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計算結(jié)果，為1。（注：B2單元格中顯示為1900/1/1，在函數(shù)欄中通過F9鍵查看，顯示結(jié)果為1。）

至此，在構(gòu)建的日期數(shù)組中，第一個滿足條件的日期出現(xiàn)在第1個位置，大家再用INDEX函數(shù)去日期值數(shù)組中，返回第1個日期值，就OK了。

第6步：通過INDEX+MATCH函數(shù)組合，完成日期匹配

B2中的公式得到最終的完善，得到最開始的公式：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))。最后，與上述步驟一致，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

這樣，令小張頭疼已久的有效期問題就解決了。

親愛的小伙伴，本文中問題的涉及到數(shù)組、MOD函數(shù)、MATCH函數(shù)、INDEX函數(shù)等綜合應(yīng)用，請務(wù)必多次練習(xí)，從而明白其中的奧義哦！

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

到期日計算，EDATE甩DATE函數(shù)兩條街！

自動提醒產(chǎn)品還有多少天過期的5種Excel方法

玩轉(zhuǎn)excel日期函數(shù)，一文看懂90%的日期運算

9條最實用的計算excel中關(guān)于日期的公式！（建議收藏）

版權(quán)申明：

本文作者阿碩；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。