周五下午5点，老板冲进办公室。
"王经理那份合同哪天到期？客户刚才来电话说我们已经断供3天了！"
我手忙脚乱打开台账，翻了5分钟——合同到期日是上周二。
这就是大多数公司合同/证件管理的现状：
合同到期日前3天，对方来催才发现忘了续签
员工居住证/签证到期，本人没注意，公司被罚款
许可证/资质证书到期，业务被叫停，损失几十万
用日历提醒？几百个日期，日历早就爆了
用人工台账？人总会忘，一忘就出事
好记性不如烂笔头，但烂笔头不如Excel公式。

今天这套Excel自动提醒系统，专门解决这些问题。
核心逻辑超简单：用TODAY()获取今天日期，用条件格式提前30天标红预警。
设置一次，永久自动提醒。不用每天检查，Excel自己帮你盯着。

核心技巧
第1招：TODAY()函数——让Excel自己"知道"今天几号
【使用场景】
你需要Excel自动判断哪些合同快到期了。但"今天几号"每天都在变，你不可能每天手动改日期吧？
【操作步骤】
在提醒表的某个单元格（比如I2），输入=TODAY()
按回车，Excel自动显示今天日期
这个日期每天自动更新，永远是最新的

【公式解释】
=TODAY() —— 不需要任何参数，Excel自动返回系统当天日期。每次打开文件，它都会重新计算。
【兼容性】
WPS：✅ 完全支持
Excel 2016+：✅ 完全支持
Excel在线版：✅ 支持
【踩坑提示】
⚠️ =TODAY() 返回的是动态日期。如果你希望Excel每次打开日期相同，且文件可以脱机使用，请使用 Ctrl+; 录入静态日期。
⚠️ =NOW() 返回的是日期+时间（带小数点），用在到期判断里会出问题。记住：算日期用TODAY()，算时间用NOW()。
【金句】

条件格式不是"格式"，是Excel的"报警器"。



第2招：条件格式——提前30天自动标红（已过期不标红）
【使用场景】
合同到期前30天，整行自动变成红色，告诉你"该续签了"。不用人工检查，Excel自动提醒。
【操作步骤】
选中合同表格的数据区域（比如A2:E100，含表头的话从A2开始）
点击「开始」→「条件格式」→「新建规则」
选择「使用公式确定要设置格式的单元格」
输入公式：=AND($E2<>"",$E2-TODAY()<=30,$E2>=TODAY())
点击「格式」，设置填充色为红色
确定，完成
【公式解释】
$D2<>""：D2不是空白（排除空白行，避免空白行被标红）
$D2-TODAY()<=30：剩余天数≤30天
$D2>=TODAY()：到期日≥今天（排除已过期，已过期不标红）
【兼容性】
WPS：✅ 路径相同（开始→格式→条件格式）
Excel 2016+：✅ 完全相同
Excel Mac：✅ 路径：菜单栏「开始」→「条件格式」
【踩坑提示】
⚠️ 公式里必须是 $D2（列绝对引用D列，行相对引用），不能写成 $D$2（那样所有行都判断第2行）。
⚠️ 条件格式的适用区域必须从第2行开始（A2），否则第1行的公式会判断第1行的到期日，导致全表标红。
⚠️ 如果不加 $D2<>"" 条件，空白行的 D2-TODAY() 会算出负数，负数≤30，空白行也会被标红。


第3招：DATEDIF函数——精确计算剩余天数
【使用场景】
标红只能告诉你"快到期了"，但你不知道还剩多少天。HR需要精确数字，好安排续签谈判时间。
【操作步骤】
在"剩余天数"列（比如F2），输入公式：=IF(E2<TODAY(),"已到期",DATEDIF(TODAY(),E2,"d"))
按回车，Excel显示剩余天数（或"已到期"）
下拉填充整列
【公式解释】
=DATEDIF(开始日期,结束日期,"d") —— 计算两个日期之间相差的天数。
第一个参数TODAY()：开始日期（今天）
第二个参数D2：结束日期（合同到期日）
第三个参数"d"：按天计算（必须英文引号）
关键约束：开始日期必须≤结束日期，否则Excel报错#NUM!。上面公式用IF先判断D2<TODAY()的情况，已到期显示"已到期"而不是报错。
【兼容性】
WPS：✅ 支持（但WPS有提示，Excel没有）
Excel 2016+：✅ 支持（隐藏函数，不提示，但能用）
Excel在线版：❌ 不支持DATEDIF
【踩坑提示】
⚠️ DATEDIF是隐藏函数，Excel不会自动提示，但能正常使用。如果手输公式不提示，别慌，继续输就行。
⚠️ 第三个参数必须加英文引号："d"（天）、"m"（月）、"y"（年）。如果写成 =DATEDIF(A1,B1,d)（没有引号），Excel报错 #NAME?。
⚠️ 如果结束日期（D2）比开始日期（TODAY()）小，DATEDIF报错 #NUM!。上面公式用IF先判断，已到期显示"已到期"而不是报错。
【金句】

DATEDIF不会弹窗提醒你，但它的计算结果会——在单元格里，每天变。



第4招：完整三色预警系统——红黄灰三级，已到期单独处理
【使用场景】
你需要的不是简单的"标红"，而是三色预警系统：
🔴 红色：剩余≤30天，立即处理
🟡 黄色：剩余31-90天，开始准备
⚪ 灰色+删除线：已到期，单独标记
【操作步骤】
选中数据区域A2:E100
「开始」→「条件格式」→「管理规则」
新建规则1（红色预警）：公式=AND($F2<>"",$F2-TODAY()<=30,$F2>=TODAY())，格式填充红色
新建规则2（黄色预警）：公式=AND($E2<>"",$E2-TODAY()>30,$E2-TODAY()<=90)，格式填充黄色
新建规则3（已到期）：公式=$E2<TODAY()，格式填充灰色+删除线字体
在「管理规则」里，用上下箭头把"规则3（已到期）"移到最下面（停止如果为真）
确定，完成

【公式解释】
规则1：$D2>=TODAY()排除已到期，只标≤30天且未到期的。
规则2：>30且<=90，黄灯预警区间。
规则3：$D2<TODAY()已到期，灰色+删除线。
【兼容性】
WPS：✅ 条件格式规则管理和Excel相同
Excel Mac：✅ 完全相同
Google Sheets：✅ 支持（但界面不同）
【踩坑提示】
⚠️ 条件格式规则的顺序很重要。Excel按规则顺序执行，如果"已到期"规则在"红色预警"上面，已到期的行会被红色覆盖。解决：在「管理规则」里，把"已到期"规则移到最下面，并勾选「如果为真则停止」。
⚠️ 如果表格有合并单元格，条件格式可能只作用于合并区域的第一行。解决：先取消合并，再设置条件格式，最后重新合并标题行。

进阶联动：条件格式+筛选+排序，打造动态预警看板
【场景描述】
光有三色预警还不够。你需要每天打开文件，第一时间看到"今天必须处理的合同"。这需要把条件格式、筛选、排序组合起来。
【操作步骤】
在"剩余天数"列（E列）点击筛选按钮
选择「数字筛选」→「介于」→ 输入0和30→ 确定（只显示剩余0-30天的合同）
再按「剩余天数」列升序排序（最小的在最上面）
每天打开文件，直接看筛选后的结果——红色行就是今天必须联系的
【联动价值】
条件格式负责"视觉提醒"（红色/黄色/灰色）
筛选负责"聚焦当前"（只看需要处理的）
排序负责"优先级"（最紧急的在最上面）


高频场景
场景1：HR员工合同到期提醒
背景：公司200个员工，合同到期日各不相同。HR需要在到期前30天启动续签谈判。
操作方法：
建立员工合同台账，字段：姓名、部门、合同开始日、合同到期日、剩余天数、提醒状态
到期日列设置条件格式（三色预警：红30天/黄90天/灰已到期）
剩余天数列：=IF(D2<TODAY(),"已到期",DATEDIF(TODAY(),D2,"d"))
提醒状态列：=IF(D2<TODAY(),"已过期",IF(D2-TODAY()<=30,"立即处理",IF(D2-TODAY()<=90,"准备中","安全")))

效果：每天打开台账，筛选"立即处理"，红色行就是本月必须处理的续签，一目了然。


场景2：行政许可证/资质证书到期预警
背景：公司有多张经营许可证、ISO证书、行业资质，到期日不同，过期会影响业务。
操作方法：
建立证书管理表，字段：证书名称、发证日期、到期日期、发证机关、剩余天数、证书状态
条件格式设置三色预警（红30天/黄90天/灰已到期）
证书状态列：=IF(D2<TODAY(),"已过期",IF(D2-TODAY()<=30,"红色预警",IF(D2-TODAY()<=90,"黄色预警","正常")))
用筛选功能，筛选"红色预警"的证书，批量安排续期
效果：每次年审前2个月打开表格，所有需要续期的证书自动标红，不用翻几百行找。

场景3：财务客户合同/付款条款到期提醒
背景：财务需要跟踪客户合同到期日和付款条款到期日，避免错过续约或催款窗口。
操作方法：
建立客户合同表，字段：客户名称、合同到期日、付款条款到期日、年费金额、行动建议
条件格式：=AND($C2<>"",$C2-TODAY()<=30,$C2>=TODAY())，红色
用SORT或筛选，按"合同到期日"升序排列，最早的合同排最上面
行动建议列：=IF(C2<TODAY(),"已过期，紧急联系",IF(C2-TODAY()<=30,"立即启动续约谈判",IF(C2-TODAY()<=90,"发提醒邮件","无")))
效果：每月初打开表格，红色行就是本月必须联系的客户，提前准备续约方案。

避坑指南
坑1：TODAY()是动态函数，文件脱机打开日期会"卡住"
问题：你在家用Excel打开文件，TODAY()显示的是你打开文件时的日期。如果你周五保存，周一再打开，TODAY()还是周五的日期（除非你手动重新计算）。
解决：每次打开文件，按 F9 强制重新计算。或者，把"今天日期"单独放在一个单元格（F1==TODAY()），条件格式引用F1，这样只需F1重新计算，不用改所有公式。

坑2：DATEDIF的第三个参数写错了，算出来是月数不是天数
问题：=DATEDIF(A1,B1,"d") 的 "d" 是字符串，必须加英文引号。如果写成 =DATEDIF(A1,B1,d)（没有引号），Excel会报错 #NAME?。
解决：记住，DATEDIF的第三参数必须加英文引号："d"（天）、"m"（月）、"y"（年）。

坑3：条件格式公式里列绝对引用忘记加$，导致整列判断错误
问题：你设置条件格式时写 $D2-TODAY()<=30，以为$D是绝对引用。但如果你选中的是A2:E100，条件格式应用到B列时，B列的公式会变成 =E2-TODAY()<=30（B列偏移3列，D列也偏移3列变成E列）。
解决：条件格式公式里，需要固定列的，写 $D2（列绝对，行相对）。需要固定行和列的，写 $D$2。需要都不固定的，写 D2。

坑4：已到期合同（剩余天数为负）还在标红，误导判断
问题：条件格式只判断 <=30，不判断是否已到期（负数也≤30）。结果已过期的合同也标红，你以为"快到期"，其实"已经过期"。
解决：在条件格式公式里加判断 $D2>=TODAY()，排除已到期。完整公式：=AND($D2<>"",$D2-TODAY()<=30,$D2>=TODAY())。

坑5：表格有筛选，条件格式不刷新，标红不准确
问题：你筛选了"部门=销售"，条件格式还是按全表计算，筛选后的行可能标红不正常。
解决：条件格式本身不受筛选影响（筛选只是隐藏行，公式照样算）。但如果你看到标红"消失"，是因为筛选后行号变了。解决：不用管，筛选取消后标红会回来。或者用「条件格式」→「管理规则」→勾选「如果为真则停止」，调整规则顺序。

这套模板我花了3天打磨，已给50+企业HR使用，今天开放给大家。内含：三色预警表+员工合同台账+证书管理表+客户合同跟踪表，4个场景全覆盖，下载即可用。

关注公众号，后台回复【资料】领取全套模板+配套练习文件。

觉得有用？转发给同事，一起告别合同到期背锅。
你们公司用什么方法管理合同/证件到期？Excel？OA系统？还是靠人记？评论区聊聊，我来帮你优化。