日常工作中,经常需要用到一些到期日提醒的功能,比如人事部门需要能够实时了解员工的合同到期情况,以便在合同到期前能够及时续签合同。搞供应商管理的,比较关心供应商资质文件是否在有效期范围内,等等。 下面我们就来演示制作一张简单的员工合同到期提醒表。 STEP 1 新建一张工作表命名为“合同”,表中内容如下图所示: 然后,将E列、G列、H列分别设置为日期格式;将F列、I列设置为常规或数值格式(保留0位小数)。 STEP 2 再新建一张工作表命名为“标准”,通过该表可自由设定各个工龄段的签约合同期限,该表内容如下图所示: STEP 3 设置F2单元格公式为“=DATEDIF(E2,TODAY(),'Y')”,用来显示员工工龄。设置好公式后,用填充手柄把下面的单元格填充满。 TODAY函数用来表示当天的日期;DATEDIF为Excel的隐藏函数,用来返回第一个参数和第二个参数之间相差的天数、月数、年数等(取决于第三个参数的值)。DATEDIF(E2,TODAY(),'Y')表示E2单元格入职日期与当前日期相差的年数,即工龄。 STEP 4 设置H2单元格公式为“=EDATE(G2,VLOOKUP(F2,标准!$A$2:$B$6,2,TRUE)*12)”,用来显示合同到期日期;设置好公式后,用填充手柄把下面的单元格填充满。 VLOOKUP函数用来查找F2单元格工龄所对应的签订合同有效期限,本例返回结果为1年、3年或5年; EDATE函数表示G2单元格合同签订日期开始,然后加上VLOOKUP函数返回的月数(年数乘以12,换算成月数)后得到的日期,即合同到期日。 STEP 5 设置I2单元格公式为“=H2-TODAY()”,用来显示合同还有多少天到期。设置好公式后,用填充手柄把下面的单元格填充满。 设置好公式后,表格效果如下: STEP 6 使用条件格式将合同到期小于10天的背景色填充为红色,看起来可以更醒目一些。 |
|