如何用函数及条件格式做一个员工合同到期模板?
根据需要输入一个提醒天数,比如60天,那么哪些员工的合同即将在60天内到期,就能立马被标注的明明白白。
这个逆天的Excel功能,你知道怎么操作吗?一起来看看吧!
下图是这个合同到期提醒的效果展示:自行设置即将到期的提醒天数,已到期用红色标注,即将到期用黄色标注。
下面就和大家一起分享制作步骤。
第一阶段、公式设置
在这个模板中一共有三处公式,分别是当前日期、合同到期日和是否到期。
当前日期的公式很简单,=TODAY()
合同到期日用到了EDATE函数,公式为=EDATE(D4,E4*12)
EDATE函数可以根据指定的一个日期计算出N个月后的对应日期,在这个模板中,指定的日期就是合同签订日期,因为合同期限是以年为单位的,所以要在合同期限上*12,换算成月数,这样得到的日期就是合同到期日。
是否到期这一列是整个模板的关键部位,要考虑的因素比较多。
首先比较当前日期是否超过了合同到期日,如果是则返回“已到期”,如果没超过就要根据输入的提醒天数判断当前日期+提醒天数是否会超过合同到期日,如果是则返回“即将到期”。否则的话不返回任何内容,直接空白。
还有个要考虑的地方是假如没有输入提醒天数或者输入有误(例如输入了负数),则不会返回即将到期,只体现已到期和空白两种情况。
看起来要判断的确实比较多,实际上使用IF嵌套的公式也不算太复杂。
公式为=IF(F4<$B$2,"已到期",IF(F4<$B$2+$F$2,IF($F$2>0,"即将到期"),""))
温馨提示:是否到期这个地方的公式还有其他的思路,有兴趣的同学不妨用这个问题去练习其他的公式解法。
设置了这些公式后,就可以进入第二阶段的操作了。
第二阶段、设置条件格式
这一个阶段就比较简单了,一共需要设置两个规则,分别针对已到期和即将到期进行标注。
1. 即将到期的设置步骤
操作要点:
①选中数据区域,使用条件格式-新建规则-使用公式确定要设置格式的单元格
②填入公式=$G4="即将到期",公式中的必须是英文状态的引号。
③设置填充色,完成即可。
2.已到期的设置步骤
可以利用已经设置的规则进行修改,操作方法如图所示。
操作要点:
使用条件格式-管理规则-重复规则,然后双击修改公式和填充色,确定即可。
至此,一个人性化的合同到期模板就做好了,你学会了吗?
联系客服