夜雨聆风学习资料网

ARTICLE · 1394955

合同还有几天到期,让Excel自己亮红灯

合同还有几天到期,让Excel自己亮红灯

上个月帮朋友公司收拾烂摊子:行政小周管着三百多份合同——办公室租赁、设备维保、供应商框架协议,全记在一张 Excel 台账里。续租通知要提前 30 天发出去,她的做法是每月 1 号打开台账,按到期日排一次序,肉眼从上往下扫。10 月她休了一周年假,回来漏了两份门面房的租赁合同,房东按约定多收了一个月租金,老板在群里连续@了她三次。

其实这种事不该靠人肉盯。Excel 自带条件格式,配合 TODAY 函数,能让台账自己亮灯:快到期的变黄,已经到期的变红,离得远的保持原样。颜色每天自动刷新——你打开文件那一刻,它按「今天」重新算一遍。全程不用装任何东西,Excel 和 WPS 表格都能做。

下面这套做法按微软官方文档的条件格式路径写,我拿一张 40 行的台账实测过,照抄就能用。

第一步:先加一列「剩余天数」

颜色是辅助,干活的人最终要看的还是「还剩几天」。在到期日右边插一列,标题写「剩余天数」。假设到期日在 D 列、数据从第 2 行开始,在第一个格输入:

=D2-TODAY()

回车后这一格可能显示成又一个日期——右键 → 设置单元格格式 → 选「常规」或「数值」,它就变成一个普通的天数,然后下拉填充到底。

正数是还剩多少天,0 是今天到期,负数是已经过期几天。月底要催办,直接按这列升序排序,最急的永远排在最上面。

第二步:给已到期和快到期的上色

选中整个数据区——注意从第 2 行数据开始选,别把表头框进去——然后照这个路径走:开始 → 条件格式 → 新建规则 → 选最后一项「使用公式确定要设置格式的单元格」,在公式框输入:

=AND($D2<>"",$D2<=TODAY())

点下面的「格式」按钮,填充选红色,确定。这一条管「已到期和今天到期」。

再来一次,新建第二条规则,公式换成:

=AND($D2<>"",$D2>TODAY(),$D2<=TODAY()+30)

填充选黄色。这条管「还没到期、但 30 天内要到期」——想提前 60 天预警,把 30 改成 60 就行。

两条规则的条件互不重叠,不用管先后顺序。做完点确定,台账瞬间分出层次:红灯催办,黄灯着手准备,没颜色的不用看。

第三步:认识一下 TODAY,别把灯养死

TODAY() 没有任何参数,返回系统当前日期。按微软官方文档的说法,它只在公式重新计算时更新——你改动任何一个格子、或者重新打开文件,颜色都会按当天的日期重算。今天亮黄的格子,二十天后哪怕你没动过文件,它自己会变红。

但有一个开关能让灯失灵:文件 → 选项 → 公式 → 计算选项,如果被设成了「手动」,TODAY 就不再自动刷新,颜色停在最后一次计算的那天。日常保持默认的「自动」就行。

五个坑,都来自实测

1、日期列是「假日期」。从业务系统导出的台账,日期经常是文本——判断方法:真日期在单元格里默认右对齐,文本左对齐。文本拿去和 TODAY() 比大小不会报错,但结果全乱。救法:选中该列 → 数据 → 分列 → 一路下一步,第三步把列格式选「日期 YMD」→ 完成。

2、只第一行变色。公式写成了 $D$2(行列全锁),或者选区不是从第 2 行开始。$ 只锁列不锁行,且公式里的行号必须等于选区第一行的行号,差一行整片错位。

3、空白行全亮红灯。到期日那格是空的,空格在 Excel 里被当成 0(也就是 1900 年 1 月 1 日),永远算「过期」。所以两条公式开头都加了 $D2<>"",这一段别省。

4、颜色不是数据。条件格式只是显示规则,把格子复制到别处只带走底色、不带规则;反过来,按颜色筛选和排序倒是照常能用,红灯几行一筛就出来。

5、发给别人看没问题,颜色会按对方打开文件那天重新算——这是优点不是毛病,30 天的黄灯对谁都是 30 天。真要留档「当时的状态」,把剩余天数列复制后右键 → 选择性粘贴 → 数值,天数就冻住了。

这套红黄绿灯不只合同能用。员工证件年检、发票认证期限、域名和证书续费、社团活动审批——凡是「按日期盯节点」的事,一张台账、三条公式全搞定。建一次,年年用。

觉得有用的话,关注「文件处理手艺人」,后台回复「工具」,可以领一个本地离线版的图片/PDF转Excel小工具,免安装不联网;回复「教程」,能看更多马上能上手的办公技巧。下次盯日期,别再靠人眼了。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料