Excel 怎么给快到期的合同加颜色提醒
合同、证件、年检的到期日可以用条件格式自动上色,讲清公式写法、行列引用怎么锁,以及三级提醒怎么排序。
合同、证件、年检这类有截止日期的事,靠人翻表很容易漏。条件格式可以按「距离今天还有多少天」自动上色,到期前三十天变黄、七天内变红,不用每天手动改填充。真正容易翻车的是公式里的引用,锁错一个符号就会整片变色或者只有一格变色。
入口在哪
- 「开始」选项卡右侧的「条件格式」,下拉里最下面一项是「新建规则」。
- 「新建规则」窗口里选「使用公式确定要设置格式的单元格」,按天数判断用这个。
- 现成入口是「条件格式 > 突出显示单元格规则 > 发生日期」,但它只能按月或周这类固定周期判断。
- 规则建好后统一在「条件格式 > 管理规则」里改顺序、改范围、临时停用。
具体步骤
- 先把到期日那列做成真日期:右对齐、能被
=TODAY()-C2直接相减,文本日期要先转格式。 - 选中要上色的区域,比如整行
$A$2:$F$200,一定从第一个数据行开始选。 - 新建规则,公式写
=AND(ISNUMBER($C2),$C2-TODAY()<=30),C 列锁列不锁行。 - 格式里只改「填充」为浅黄,确定;同法再建一条把 30 改成 7 的规则,填红色。
- 在红色那条规则上勾「停止如果为真」,让七天内优先显示红色,不再被黄色覆盖。
- 进「管理规则」用上下箭头把顺序排成:已过期、七天内、三十天内。
几个坑
- TODAY() 是易失函数,文件每天打开时会重算,但关着的时候不会动,别把它当成会响的闹钟。
- 区域从第二行选、公式却写
$C$1,整体会错一行,表现为颜色比实际晚一天。 - 行列锁错会只有第一个单元格变色,或者整片区域一起变成红。
- 多条规则叠加时没勾「停止如果为真」,后建的规则会盖掉先建的,颜色看起来不受控。
怎么验证
找一条已知还有二十天到期的记录,看它是不是黄色;再把某条的日期临时改到三天后,颜色应当立刻变红。看完把日期改回原值并保存,别把测试数据留在正式表里。规则只对当前工作表或指定区域生效,换到别的工作表时要确认应用范围已经带上。
常见问题
为什么只有第一行变色
公式里的行号被绝对引用锁住了。作用于整片区域的公式,列可以用 $C 锁住,行号必须留成相对引用 C2,这样每一行才会拿自己那一格去算天数。
能不能提前六十天提醒续签
可以,把公式里的 30 改成 60 单独建一条规则,颜色选浅一点做背景提醒。三条以上规则要按紧急程度排序,靠「停止如果为真」控制覆盖关系。
日期是文本格式怎么办
文本日期不参与减法,公式会算出错误值。可以选中该列用「数据 > 分列」一路下一步到完成,把它转成真日期,也可以先用 DATEVALUE 转换再判断。
一句话总结:给到期日建一条基于 TODAY() 的条件格式规则,锁列不锁行,再用停止如果为真分出三级提醒。