試算表中的退休日期,是指某人自出生日期起達到自訂整數年齡的行事曆日期,而最簡單的 Excel 公式就是 =EDATE(birthdate, 12*retirement_age)。這個公式只需要一個儲存格中的出生日期,再將另一個儲存格中的目標退休年齡乘以 12 個月,就能回傳該年齡所落在的精確日期,並以一般 Excel 日期格式顯示,因此你可以進行相減、排序,或作為其他公式的輸入。DATE 函數提供另一種做法,將整數年數加到年份部分,同時保持月份與日期與出生日相同,這也符合大多數法定退休年齡的慣例。無論使用哪一個公式,你都能結合 TODAY() 與 DATEDIF 產生以年、月、日為單位的即時倒數計時,並用 TEXT() 印出退休日期是星期幾。本文將並列介紹 EDATE 與 DATE 兩種做法,接著示範 DATEDIF 倒數計時,以及 Excel 處理 29 日生日的閏年邊界情況:DATE 函數會自動將 2 月 29 日往前推到 3 月 1 日。

how to calculate retirement date in excel
如何在 Excel 中計算退休日期:EDATE 與 DATE

計算退休日期的兩個 Excel 公式

這兩個公式採用相同的兩個輸入:A2 儲存格中的出生日期,以及 B2 儲存格中的退休年齡,但加總年份的方式略有不同。請根據貴公司或個人計畫計算里程碑的方式擇一使用。

EDATE 做法。公式 =EDATE(A2, 12*B2) 將退休年齡視為月數,因此將年齡乘以 12 就會把「年」轉成「月」,EDATE 則會回傳該月數之後的同一個日期。倘若目標月份沒有對應的日期,EDATE 會往前回推至該月最後一天,因此出生於 2 月 29 日、而目標年並非閏年的人,將會得到 2 月 28 日。

DATE 做法。公式 =DATE(YEAR(A2)+B2, MONTH(A2), DAY(A2)) 會依照年、月、日三個組成部分重新組合日期,因此不論月份長短,月份與日期都會與出生日相同。在 Excel 中要求取得平年的 2 月 29 日,結果會自動推進至 3 月 1 日,這是個值得在確認人力計畫前先了解的已知特性。

面向EDATE 公式DATE 公式
輸入項出生日期、年齡 × 12 個月出生日期組成部分,加上以年為單位的年齡
月份與日期是否與出生日相同?是,但月底日期可能進位是,但 Excel 對 2 月 29 日有推進至 3 月 1 日的特性
月底的生日自動調整為較短月份的最後一天保留日期,但在 Excel 中會往前推進
平年的 2 月 29 日往前回推至 2 月 28 日往前推進至 3 月 1 日 (Excel 預設行為)
最適用情境使用固定年齡的大量薪資或人力名單需要嚴格保留日期的個人規劃

逐步在 Excel 中建立退休日期欄位

以下六個步驟將帶領你從空白工作表,到一個能自動計算每位員工退休日期的欄位。

  1. 開啟空白活頁簿,在第 1 列設定標題:A1 = 姓名、B1 = 出生日期、C1 = 退休年齡、D1 = 退休日期。
  2. 將每個人的出生日期輸入至 B 欄,採用真正的 Excel 日期格式,例如 01/01/1990;C 欄的退休年齡則輸入整數,例如 60、62、65 或 67。
  3. 在 D2 儲存格輸入 =EDATE(B2, 12*C2) 並按下 Enter。Excel 會在編輯欄右側以序列值顯示退休日期。
  4. 選取 D 欄,點右鍵選擇「儲存格格式」,接著挑選日期類型與像 dd mmm yyyy 這樣的格式,讓 01/01/2055 能清楚顯示。
  5. 抓住 D2 的填滿控點,將公式複製到整個欄位。每列只要按下 Enter 就會自動更新。
  6. 若要切換方法,請將 D2 的公式替換為 =DATE(YEAR(B2)+C2, MONTH(B2), DAY(B2)),再以同樣方式複製下來,便能比較兩種結果。

若你完全不想輸入公式,退休日期計算機會以整數的年-月-日運算來處理,並對非閏年目標年的 2 月 29 日生日採取 2 月 28 日的退讓機制;只要輸入出生日期和目標年齡,就能立刻在同一個畫面上看到退休日期、星期幾與倒數計時。

使用 TODAY 與 DATEDIF 倒數退休日

只有能顯示「還有多久」的退休日期才真正實用。兩個簡單的公式能將 D 欄的靜態日期,轉換為每次開啟活頁簿都會更新的即時倒數計時。

剩餘總天數。公式 =D2 - TODAY() 在未來退休日時會回傳正數天數;若是已過期,則回傳負數。將儲存格格式設為無小數點的數值,就能得到乾淨的整數。

細分為年、月、日。可使用 =DATEDIF(TODAY(), D2, "Y") & " years, " & DATEDIF(TODAY(), D2, "YM") & " months, " & DATEDIF(TODAY(), D2, "MD") & " days"。DATEDIF 是 Excel 中一個隱藏的函數,運作完全符合其命名;"Y"、"YM" 與 "MD" 三個單位分別代表完整的年數、剩餘月數與剩餘天數,因此這三段數字相加永遠剛好等於總天數。對於正在規劃最後工作日的人來說,計算今天與退休日期之間的工作日通常比計算行事曆天數更實用。

退休日期是星期幾。公式 =TEXT(D2, "dddd") 會將序列值轉為完整的星期名稱,例如「星期五」,這在規劃週五歡送會或週一早上的通知時相當方便。

閏年生日與其他 Excel 邊界情況

Excel 的 DATE 函數會特別處理 2 月 29 日:當你在平年中要求 DATE(2065, 2, 29) 時,Excel 會回傳 2065 年 3 月 1 日,而非大多數人預期的 2 月 28 日,因為它會將超出 (29 − 28 = 1) 的部分加到 2 月的最後一天。若你的退休金計畫或個人規劃採用 2 月 28 日的慣例,就必須在公式外加上一段簡短的防護。

適用於任何 2 月 29 日生日的安全公式。請使用 =IF(AND(MONTH(B2)=2, DAY(B2)=29), IF(ISLEAPYEAR_YEAR, DATE(YEAR(B2)+C2, 2, 29), DATE(YEAR(B2)+C2, 2, 28)), DATE(YEAR(B2)+C2, MONTH(B2), DAY(B2)))。Excel 並未內建 ISLEAPYEAR 函數,因此閏年判斷通常會用巢狀 IF 檢查 MOD(YEAR(B2)+C2, 4) 與 MOD(YEAR(B2)+C2, 400) 是否為零。退休日期計算機會自動套用同樣的 2 月 28 日退讓機制,這也是為何許多人在處理棘手的生日時會交互比對兩者。

另外還有兩個值得了解的邊界情況:

  • 月底日期進位。出生於 3 月 31 日、65 歲退休的人,其退休日期仍會顯示 3 月 31 日,因為兩個月份都是 31 天。
  • 已經退休。D2 − TODAY() 出現負數僅代表已達到退休年齡,這對分階段退休時程、退休後福利檢查,或驗證前員工是否真的已達到目標年齡都很實用。

需要將這些公式套用於大量資料列的人,也應該了解如何在 Excel 中依出生日期計算年齡,因為退休年齡的輸入項經常是由另一個公式計算得來,而非逐列手動輸入。

範例演算:1990 年 1 月 1 日出生,65 歲退休

我們用單一列來逐步走過整個計算過程。假設 B2 儲存格為 01/01/1990,C2 儲存格為 65。

步驟 1。選定公式。使用 EDATE:=EDATE(B2, 12*C2) 會變成 =EDATE("01/01/1990", 780)。

步驟 2。代入計算。EDATE 將 780 個月,亦即 65 × 12,加到 01/01/1990 上。自 1990 年起算 65 年為 2055 年,月份與日期則維持 1 月 1 日。

步驟 3。得到結果。退休日期為 01/01/2055。

步驟 4。同列改用 DATE:=DATE(YEAR(B2)+C2, MONTH(B2), DAY(B2)) 會變成 =DATE(1990+65, 1, 1) = DATE(2055, 1, 1) = 01/01/2055。

因為 1 月 1 日並無歧義,兩個公式結果一致。只有在月底前後的生日,或是 2 月 29 日這類與當月天數有關的情境時,公式才會出現分歧,這時 Excel 的預設行為就會開始產生影響。

何時瀏覽器工具比試算表更快

當你已經在維護一份數百列的員工名單時,Excel 無可取代;但若只是個人一次性規劃,使用瀏覽器版的計算機會更快。退休日期計算機採用相同的兩個輸入,能在同一個畫面顯示日期、星期幾與倒數計時,且頁面載入後即可離線運作,你輸入的內容並不會上傳到伺服器。

情境最佳選擇
80 位員工、年齡不同的 HR 名單可排序與篩選的 Excel EDATE 欄位
一次性個人退休規劃瀏覽器版退休日期計算機
需要連結 TODAY() 的即時倒數引用儲存格的 Excel DATEDIF 公式
想快速測試 60、65、67 歲等不同情境瀏覽器版計算機,調整年齡結果立刻更新
受法定退休金規範約束的 2 月 29 日生日瀏覽器版計算機搭配 2 月 28 日退讓機制,再以 Excel 驗證

將兩者並用是常見的模式:以 Excel 建立正式的紀錄工作表,並用瀏覽器工具逐一交叉檢查,或即時模擬「假如我改成 60 歲而非 65 歲退休會如何」這類情境,省去到處複製公式的麻煩。