Zoranta指南
CertAlert

CertAlert › 指南

證照管理 Excel:到期日、剩餘天數與顏色提醒的做法

證照管理的 Excel 表,核心只有四欄:證照名稱、到期日、剩餘天數、狀態。剩餘天數用「到期日減今天」算出,狀態用 IF 依天數分級,再用條件式格式把快到期的列塗上顏色,一打開就知道該先處理哪一張。

Excel 的限制是:它不會主動通知你。表格做得再好,不打開就看不到。下面先把表做好,最後再說怎麼補上提醒。

掛曆上標著小鈴鐺的一格,下面放著懷錶與手機

證照管理 Excel 的欄位設計

一張證照一列。欄位不必多,但下面這些放進去,換證時就不用到處找資料。

欄欄位名稱內容
A證照名稱例:職業駕照、護理師執業執照、AWS 認證
B持有人家人或團隊共用一張表時才需要
C發證機關換證時要聯絡的單位
D到期日用日期格式輸入,不要打成文字
E剩餘天數公式自動算
F狀態公式自動分級
G換證費用規費、課程費、體檢費等
H備註需要的文件、上課積分、上次辦理的地點

沒有到期日的證照,D 欄留空,公式要能處理空白,下面的寫法已經考慮進去。

CertAlert

CertAlert 做的事和這張表一樣(依剩餘天數用紅、橘、藍、綠分級),另外會在到期前主動發送提醒通知,不必記得去打開檔案。 在 Google Play 下載

剩餘天數與狀態的公式

假設資料從第 2 列開始。在 E2 輸入剩餘天數的公式,往下拉到最後一列:

E2: =IF(D2="","",D2-TODAY())

結果是正數代表還有幾天到期,0 是今天到期,負數代表已經過期幾天。儲存格格式要設成「數值」,不然會顯示成日期。

在 F2 依天數分級:

F2: =IF(E2="","無期限",IF(E2<0,"已過期",IF(E2<=30,"30 天內",IF(E2<=90,"90 天內","有效"))))

30 天和 90 天是常見的分界,可以依自己的換證準備時間調整。需要上課、體檢、等審核的證照,分界放寬一點比較安全。

用條件式格式做顏色提醒

  1. 選取 A2 到 H 欄的最後一列(整個資料範圍)。
  2. 在「常用」→「條件式格式設定」→「新增規則」,選「使用公式來決定要格式化哪些儲存格」。
  3. 輸入 =$F2="已過期",格式選紅色填滿。
  4. 再新增規則 =$F2="30 天內",選橘色;=$F2="90 天內",選藍色。
  5. 最後依「剩餘天數」由小到大排序,快到期的會排在最上面。

公式裡的 $F2 要鎖欄不鎖列,整列才會一起變色。

Excel 證照到期提醒的極限與補法

這張表能告訴你「打開的那一刻」誰快到期,但不會在你沒打開時通知你。常見的補法有:

證照只有兩三張時,Excel 加行事曆就夠用。張數多、每張需要的準備時間不同,或想在手機上隨時看時,用專門的工具會省事。各類證照的實際辦理方式,可以參考護理師執照更新與職業駕照審驗。

CertAlert

不想維護表格,就讓手機提醒你

CertAlert 只要輸入名稱與到期日就能登記證照,換證後會依相同週期建議下一個到期日,並估算未來 12 個月的換證費用。

在 Google Play 下載

iPhone 版即將推出

更多指南

所有指南

本頁由 CertAlert 的開發者 Zoranta 撰寫。