大家好，我是小魚。

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

具體操作步驟：

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

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

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

解析：

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

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

第二步、自動計算合同到期天數(shù)

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

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

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

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

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

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

解析：

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

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

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

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

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

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

圖8

解析：

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

=$G3="已到期"

=$G3="即將到期"

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

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