大家好，我是小魚。

今天分享一下如何利用Excel制作合同到期提醒表，實現Excel表格自動計算合同到期日和天數，根據合同狀態(tài)和到期天數自動填充顏色提醒，超實用。先看一下效果，已經到期的合同會自動被填充為紅色，即將過期的合同會自動被填充為黃色。

具體操作步驟：

第一步、自動計算合同到期日期

1、首先合同【簽約日期】和【到期日期】下面的數據必須是日期格式，不能是其它的格式否則無法計算，如果是其它格式需要轉換成標準的日期格式，如下圖所示

2、在“到期日期”下面的第一個單元格中輸入公式：=EDATE(D3,E3)-1 然后填充向下填充剩下的數據即可， 如下圖所示

解析：

①EDATE(開始日期, 月數) 函數是用來計算某個日期指定月份之后的日期。第一個參數開始日期:就是合同開始日期D3，第二個參數月：就是合同服務期月數E3。

②在公式后面-1是因為，合同一般都是提前一天。

第二步、自動計算合同到期天數

在【到期天數】下面的第一個空白格中輸入公式=F3-TODAY()，這里的F3就是前面的合同到期日，TODAY函數是得出今天的日期，利用兩者之差算出到期天數，然后再填充下面的數據即可，如下圖所示

第三步、設置合同狀態(tài)

我們可以為合同設置未到期/即將到期/已到期（大于30天設置為未到期，30天之內設置為即將到期，具體時間可以根據時間情況設置）

1、在【合同狀態(tài)】下面的第一個空白單元格中，輸入公式

=IF(H3>30,"未到期",IF(H3>0,"即將到期","已到期"))

然后回車，填充其它空白單元格。

解析：

=IF(H3>30,"未到期",IF(H3>0,"即將到期","已到期"))

這個公式中H3代表到期天數這一列，上面的公式含義是，第一個IF是當到期天數大于30天時提示合同狀態(tài)“未到期”；然后第二個IF就是當到期天數大于0小于30天合同狀態(tài)為："即將到期"；如果上面兩個的條件都不滿足合同狀態(tài)為：“已到期”。

第四步、根據合同狀態(tài)設置自動填充顏色合同顏色

1、首先選中除標題行以外的其它數據區(qū)域，然后點擊【開始】菜單欄下的【條件格式】工具欄中的“新建規(guī)則選項“，如下圖

2、在彈出的“新建格式規(guī)則對話框” ，在規(guī)則類型下拉菜單中選擇“使用公式確定要設置格式的單元格”。輸入公式=$G3="已到期"，點擊下面的“格式”選擇紅色，點擊確定，這樣到期的合同就會自動填充成紅色了。

3、設置合同“即將到期”狀態(tài)時填充黃色，重復以上1-2步驟，只是在第2步時公式輸入=$G3="即將到期"，在下面的“格式”選擇黃色，點擊確定，這樣“即將到期”的合同就會自動填充成黃色了。如下圖所示

圖8

解析：

根據合同狀態(tài)設置自動填充顏色合同顏色，主要是使用了條件格式，公式分別使用了

=$G3="已到期"

=$G3="即將到期"

$G3就是合同到期的狀態(tài)數據，鎖定類不鎖定行，在列前面加$,然后設置對應的填充顏色即可。

希望這篇文章能對你有所幫助。點贊收藏不迷路呦！