[函數] 如何在 Excel 中使用 DATEDIF 計算年資並進位

問題：

前幾天，LINE Meiko微課頻道 社群網友問到一個年資問題，
須使用 DATEDIF 公式來計算年資，規則是：「年資多一天算多一個月」，該如何自動進位成多一個月 ?

話說，LINE社群 Meiko微課小綠群(粉絲交流群) 裡，張忍大師 回覆社群網友，如果年資是【1年1個月0天】的細節該如何處理 ? 這才讓我意識到原來函數還要處理這個細部環節。

如何用「月」的方式表示年資：

DATEDIF 函數的基本用法

DATEDIF 函數是一個功能強大的工具，可以用來計算兩個日期之間的不同日期差異類型。其基本語法如下：

=DATEDIF(開始日期, 結束日期, "M")

此公式會計算兩個日期之間的完整「月數」。然而，如果您的需求是「多出一天就進位一個月」，那麼僅僅使用 "M" 作為參數是不夠的。

如何實現自動進位的公式

為了解決這個問題，可以結合兩個 DATEDIF 函數來計算整月數和剩餘的天數，然後進行進位操作。以下是詳細的公式設計：

1. 使用 DATEDIF(A2, B2, "M") 計算起始日至今天的完整月數。
2. 使用 DATEDIF(A2, B2, "MD") 計算完整月數後剩餘的天數。
3. 若多出的天數大於 0，表示已進入下一個月，即使還沒滿整月也需要進位加一個月。

完整公式如下：

=DATEDIF(A2, B2, "M") + IF(DATEDIF(A2, B2, "MD") > 0, 1, 0)

公式解釋

• DATEDIF(A2, B2, "M")：計算從入職日期（A2）到今天為止的完整月數。
• DATEDIF(A2, B2, "MD")：計算在完整月數基礎上剩餘的天數。如果此天數大於 0，表示有剩餘天數需要進位。
• IF(DATEDIF(A2, B2, "MD") > 0, 1, 0)：當剩餘天數大於 0 時，進位 1 個月。

舉例說明

假設入職日期為 2023/1/15，今天的日期是 2024/2/16：

• 計算整月數：
• • =DATEDIF("2023/1/15", "2024/2/16", "M") → 得到 13 個月。
• 剩餘天數：
• • =DATEDIF("2023/1/15", "2024/2/16", "MD") → 得到 1 天。
• 最終結果：
• • 13 + 1 = 14 個月。

透過這個公式，您可以自動將多出一天的情況進位成多一個月。

如何用「年 + 月」的方式表示年資：

如果想要將總月數轉換為「X 年 Y 月」的格式，可以使用 INT 和 MOD 函數來實現。

以下為完整公式：

=INT((DATEDIF(A2, B2, "M") + IF(DATEDIF(A2, B2, "MD") > 0, 1, 0))/12) & "年" & MOD((DATEDIF(A2, B2, "M") + IF(DATEDIF(A2, B2, "MD") > 0, 1, 0)), 12) & "個月"

公式解釋

• INT(.../12)：計算總月數除以 12，取得整年數。
• MOD(..., 12)：計算總月數除以 12 的餘數，得到剩餘的月數。
• & "年"、& "個月"：將年數和月數以文字形式組合，形成「X 年 Y 個月」的顯示格式。

優化公式的寫法

為了讓公式更加簡潔，可以去掉 IF 函數，直接利用邏輯判斷來實現進位。

優化後的公式如下：

=INT((DATEDIF(A2, B2, "M") + (DATEDIF(A2, B2, "MD") > 0))/12) & "年" & MOD(DATEDIF(A2, B2, "M") + (DATEDIF(A2, B2, "MD") > 0), 12) & "個月"

公式優化解釋

• DATEDIF(A2, B2, "MD") > 0：這個邏輯判斷會直接回傳 TRUE（1）或 FALSE（0），用來決定是否進位，而不需要再使用 IF 函數。

這樣可以保持公式的簡潔，同時仍然達到「多一天算一個月」的效果。

總結

通過使用 DATEDIF 函數，並結合邏輯判斷和其他 Excel 函數，可以輕鬆地計算員工的年資，並將其顯示為「年 + 月」的形式。這樣的計算方式適合需要精確處理年資進位的情況，尤其是當規則要求「多一天就進位」時。

檔案下載 (免費下載)

彩蛋

AI 真是神 !!

參考文獻

1. 張忍大師所提供的 Excel 檔案
2. ChatGPT o1 pro + ChatGPT 4o with canvas

以上就是這次的分享，請持續關注 我 和 Meiko微課頻道，謝謝大家 ~

有任何問題，請到【opa的沙龍】的【Excel 函數 情境學習】一起討論有關 函數 的問題，或加入 LINE社群 Meiko微課小綠群(粉絲交流群)，歡迎您的加入。