贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到在 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)

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(4) 人氣()


留言列表 (4)

發表留言
  • Y
  • 請問
    假如要比較的日期有兩個
    要以比較晚的那個日期為準
    也可以用這個函數下去改嗎?
  • 可以具體說明?

    vincent 於 2017/02/14 09:15 回覆

  • 訪客
  • 假如
    有預計交貨跟實際交貨兩種日期
    然後月份函數要用上面那兩個日期去代
    不過條件是
    已經有實際交貨日就用實際交貨日的日期
    尚未交貨就用預計交期
    如果尚未交貨完畢的話
    以預計交貨為主
    未交貨完畢的日期如果早於預計交期
    也以預計交期為主
    在原始的檔案中
    預計交期有分生管跟採購兩個
    也以較晚的為主


    EX:
    狀態:已進貨
    預計交貨日2/13
    實際交貨日2/26
    月份:3

    狀態:待進貨
    預計交貨日3/26
    實際交貨日3/13
    月份:3

    狀態:待進貨
    預計交貨日3/26
    實際交貨日3/13
    月份:4
  • 儲存格B2:已進貨/待進貨
    儲存格C2:預計交貨日
    儲存格D2:實際交貨日

    公式:=IF(B2="已進貨",MONTH(D2)+(DAY(D2)>25)*(1-(MONTH(D2)=12)*12),MONTH(C2)+(DAY(C2)>25)*(1-(MONTH(C2)=12)*12))

    vincent 於 2017/02/14 13:31 回覆

  • 訪客
  • 如果要符合的文字有
    已進貨跟數量不足銷單兩種
    要怎麼改?
    如果預計交貨日有2個
    要以較晚的為主
    可以嗎?


    還有在待進貨的情況下
    有辦法用函數判斷哪個日期較晚
    就用哪個日期的月份嗎?

    就跟上面提到的第三種EX一樣

    狀態:待進貨
    預計交貨日3/26
    實際交貨日3/13
    月份:4

    真的很不好意思~
  • 訪客
  • 還是EXCEL函數沒辦法這樣設呢?
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼