2017年2月13日 星期一

Excel-每月25日後算為下個月(MONTH,DAY)

網友問到在 Excel 中的問題:如何將每個 25 日後的日期算為下個月?參考下圖,3/30要傳回 4 月,而 12/27 要傳回 1 月。
Excel-每月25日後算為下個月(MONTH,DAY)

【公式設計與解析】
儲存格C2:
=MONTH(A2)+(DAY(A2)>25)*1-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12
複製儲存格C2,貼至儲存格C2:C23。
(1) (DAY(A2)>25)*1
利用 DAY 函數取出儲存格A2中日期的日數,利用條件 DAY(A2)>25 判斷日數是否大於25,傳回 TRUE/FALSE 。經由『*1』運算,則 TRUE/FALSE 會轉換為 1/0
(2) MONTH(A2)+(DAY(A2)>25)*1
利用 MONTH 函數取出儲存格A2中日期的月數,再加上判斷日數是否超過 25,若是,則+1;若否,則+0。
(3) ((MONTH(A2)+(DAY(A2)>25)*1)>12)*12
由於 12 月 25 日後應顯示為 1 月,所以判斷 MONTH(A2)+(DAY(A2)>25)*1 是否大於 12,再乘以 12 後,會傳回 12 或是 0。公式中的「-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12」乃將第(2)式的傳回值 13 再減 12。
原公式也可以改寫為,儲存格C2:
(1) =MONTH(A2)+(DAY(A2)>25)*1-(MONTH(A2)=12)*(DAY(A2)>25)*12
(2) =MONTH(A2)+(DAY(A2)>25)*(1-(MONTH(A2)=12)*12)

沒有留言:

張貼留言

好康東東