Excel——项目管理，设置时间到期自动提醒及颜色高亮_excel如何设置合同到期日计算

效果图

第一步、自动获取合同到期日期

1、首先合同【签约日期】和【到期日期】下面的数据必须是日期格式，不能是其它的格式否则无法计算，如果是其它格式需要转换成标准的日期格式，如下图所示。

2、在“到期日期”下面的第一个单元格中输入公式：=EDATE(D3,E3)-1 然后填充向下填充剩下的数据即可， 如下图所示。

 

公式解释：

①EDATE(开始日期, 月数) 函数是计算与发行日处于一月中同一天的到期日的日期。第一个参数开始日期:就是合同开始日期D3，第二个参数月：就是合同服务期月数E3。
②在公式后面-1是因为，合同一般都是提前一天。

第二步、自动合同到期天数

在【到期天数】下面的第一个空白格中输入公式=F3-TODAY()，这里的F3就是前面的合同到期日，TODAY函数是得出今天的日期，利用两者之差算出到期天数，然后再填充下面的数据即可，如下图所示。

 第三步、设置合同状态

我们可以为合同设置未到期/即将到期/已到期（大于30天设置为未到期，30天之内设置为即将到期，具体时间可以根据时间情况设置）

1、在【合同状态】下面的第一个空白单元格中，输入公式

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

然后回车，填充其它空白单元格。

公式解析：

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

这个公式中H3代表到期天数这一列，上面的公式含义是，第一个IF是当到期天数大于30天时提示合同状态“未到期”；然后第二个IF就是当到期天数大于0小于30天合同状态为："即将到期"；如果上面两个的条件都不满足合同状态为：“已到期”。

第四步、根据合同状态设置自动填充颜色合同颜色

1、首先选中除标题行以外的其它数据区域，然后点击【开始】菜单栏下的【条件格式】工具栏中的“新建规则选项“，如下图。

 2、在弹出的“新建格式规则对话框” ，在规则类型下拉菜单中选择“使用公式确定要设置格式的单元格”。输入公式=$G3="已到期"，点击下面的“格式”选择红色，点击确定，这样到期的合同就会自动填充成红色了。

 3、设置合同“即将到期”状态时填充黄色，重复以上1-2步骤，只是在第2步时公式输入=$G3="即将到期"，在下面的“格式”选择黄色，点击确定，这样“即将到期”的合同就会自动填充成黄色了。如下图所示。

解释：

根据合同状态设置自动填充颜色合同颜色，主要是使用了条件格式，公式分别使用了
=$G3="已到期"
=$G3="即将到期"
$G3就是合同到期的状态数据，锁定类不锁定行，在列前面加$,然后设置对应的填充颜色即可。