夜雨聆风学习资料网

ARTICLE · 1383625

Excel算合同剩余天数别用DATEDIF,WorkBuddy换成相减,过期不再报错

Excel算合同剩余天数别用DATEDIF,WorkBuddy换成相减,过期不再报错

花名册里某一格的「剩余天数」突然变成了 #NUM!。你第一反应是公式写错了,检查半天,公式没问题。

再一看那一行,是上个月刚离职的同事。他的合同到期日在过去,而这一列的公式是 DATEDIF。

碰到这个错,很多人的处理是把行删掉。可离职的人恰恰是最该留着记录的,删了以后查历史合同就断了。

算剩余天数这件事,方向是反的,就不该用 DATEDIF。换成最简单的减法,一格都不会报错。

01 DATEDIF 只擅长一个方向

DATEDIF 的写法是 DATEDIF(开始日期, 结束日期, "d"),含义是「从开始到结束,过了多少天」。它的前提是开始早于结束。

合同剩余天数的场景里,「开始」是今天,「结束」是到期日。到期日一旦早于今天,前提就破了,它返回 #NUM!,不给你负数。

这个函数还有一个特点:它是个隐藏函数,你输入 =DATEDIF 时 Excel 不会给参数提示,全靠记。它是为兼容早期表格软件保留下来的,一直没进正式函数列表。

它并不是没用。算工龄、算司龄、算「入职满几年」,方向永远是从过去到现在,用 DATEDIF 正合适,还能按 "y" 直接取整年。

02 换成减法,负数才有意义

日期在 Excel 里本质就是数字,可以直接减。剩余天数的公式写成:

=IF($H2="","",$H2-TODAY())

H 列是合同到期日。这句读起来就是「到期日减今天」。结果是正数,说明还剩几天;结果是负数,说明已经过期几天。

负数在这里有用,它直接告诉你已经过期几天,根本不报错。

如果想让这一列直接能读,套一层 IF 把话说全:

=IF($H2="","",IF($H2<TODAY(),"已过期 "&TODAY()-$H2&" 天",$H2-TODAY()&" 天后到期"))

这时候要留意单元格格式。日期相减的结果默认可能被识别成日期,显示成一串 1900 年开头的怪东西。选中这一列,把格式改成「常规」就正常了。

一个判断标准:要从过去算到现在,用 DATEDIF。要从现在算到未来,用减法。

03 三档颜色,顺序错了等于没配

剩余天数算出来之后,同一个数据区要加三条条件格式,逐条新建,都用公式。

档位与公式
样式
=AND($H2<>"",$H2<TODAY())
深红底,已过期
=AND($H2<>"",$H2-TODAY()<=30)
红字,30 天内到期
=AND($H2<>"",$H2-TODAY()<=90)
黄底,90 天内到期

三条都是「使用公式确定要设置格式的单元格」,作用于同一个范围

顺序很关键。规则是从上往下依次判断的,所以必须按「过期、30 天、90 天」这个严到宽的顺序排,并且每条都勾上「如果为真则停止」。

把 90 天那条放在最上面,又不勾停止,所有快到期的人都会是黄色,红色一条都出不来。看起来配了三档,实际只有一档生效。

04 加一列「已提醒」,把催办也留在表里

只有颜色还不够。同一批 30 天内到期的人,周一被提醒一次,周三你又看到还是红的,于是再催一遍。对方会觉得你在瞎催。

加一列「处理状态」,用数据验证做下拉,选项就四个:未提醒、已提醒、已续签、已离职。催过就改成已提醒,颜色还在,但你知道这件事已经启动过了。

更进一步,可以让颜色把「已处理」排除掉,公式多加一个条件:

=AND($H2<>"",$H2-TODAY()<=30,$K2="未提醒")

这样标红的那一片,永远只剩「还没人管」的人。这张表就从一份静态名单,变成了一个待办清单。这个转变比配色重要得多。

05 TODAY() 不打开就不重算

TODAY() 是易失函数,文件关着的时候它不会自己更新。你上周五看到「还剩 3 天」,周一打开才变成「已过期 1 天」。中间这两天,表面看是静止的。

这意味着这张表解决不了「主动提醒」,它只在有人打开的时候才说话。想让它主动,得加一条定时任务。

我目前的做法是每周一早上让 WorkBuddy 跑一次:读表,筛出 90 天内到期的,导成一张单独的待办表,顺手把已过期的排在最前面。

▍发给 WorkBuddy 的提示词(文件拖进对话框,复制下面这段粘贴发送)

打开「员工花名册.xlsx」。请做四件事,每做完一件报一次结果。 一,先看「剩余天数」这一列是不是用 DATEDIF 写的。如果是,把整列换成 =IF($H2="","",$H2-TODAY()),并告诉我改了多少行。 二,检查这一列的单元格格式,如果是日期格式,改成常规。 三,在 A2:J500 上加三条条件格式,按顺序:过期深红底、30 天内红字、90 天内黄底,每条都勾「为真则停止」。 四,做完筛出 90 天内到期和已过期的行,按剩余天数从小到大导出成新文件「合同待办.xlsx」,只保留姓名、部门、到期日、剩余天数、处理状态五列。

第一件事那句「告诉我改了多少行」是给自己留个凭据。改完之后你拿行数对一下花名册总人数,少了就说明有空行被跳过,或者有人的到期日是文本没参与计算。

如果这一列出现 #VALUE!,说明 H 列里有文本格式的日期。这种情况加一层容错:=IFERROR($H2-TODAY(),"日期格式待修"),让错误显性化,别让它悄悄变成 0。

06 三句实话

第一,这套东西适合几百行的手工台账。到了上千行、还要多人同时维护,你应该考虑专门的合同管理系统,Excel 加定时任务只会越来越难维护。

第二,定时任务只解决了「找出来」,催还是得人来催。它把「谁快到期」这件事从记忆里搬到清单上,剩下的沟通它替不了。

第三,我没在 HR 系统导出的原始表上完整试过。那种表常带隐藏列和文本日期,第一次跑之前,先看一遍有没有 #VALUE!,别直接信导出的待办清单。

你表里那一列,现在用的是 DATEDIF 还是直接减?评论区说说踩过哪个坑。

这里是廖若水,每周实测一个 AI 工具。

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

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

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

相关学习资料