大家好，我是小魚。

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

具體操作步驟：

第一步、自動(dòng)計(jì)算合同到期日期

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

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

解析：

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

②在公式后面-1是因?yàn)?#xff0c;合同一般都是提前一天。

第二步、自動(dòng)計(jì)算合同到期天數(shù)

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

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

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

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

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

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

解析：

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

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

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

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

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

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

圖8

解析：

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

=$G3="已到期"

=$G3="即將到期"

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

希望這篇文章能對(duì)你有所幫助。點(diǎn)贊收藏不迷路呦！