合同到期提醒公式？

fxgg72

如题：见附件。

谢谢各位大神老师的帮助！！！

求：合同到期公式？.zip (8.46 KB, 下载次数: 14)

2025-11-3 15:53 上传

点击文件名下载附件

szl3178

=TEXT(DATEDIF(D5,E5,"D"),"[>30] ;距离合同到期还有0天")

cnppp

=IF(E5-TODAY()>0, "距离合同到期还有" & E5-TODAY() & "天",
IF(E5-TODAY()=0, "合同到期",
IF(E5-TODAY()<0, "合同已过期" & ABS(E5-TODAY()) & "天")))

cnppp

这个改进30天以内的提示，超过30天为空：
=IF(E5="","",IF(TODAY()>E5,"合同已过期"&TODAY()-E5&"天",IF(TODAY()=E5,"合同今天到期",IF(TODAY()>=E5-30,"距离合同到期还有"&E5-TODAY()&"天",""))))

也可以改变一下：
=IF(E5="", "", CHOOSE(1 + (TODAY()>E5) + 2*(TODAY()=E5) + 3*(E5-TODAY()<=30)*(TODAY()<E5),"", "合同已过期"&TODAY()-E5&"天","合同今天到期","距离合同到期还有"&E5-TODAY()&"天"))

mykitami

=IFERROR("还有"&DATEDIF(TODAY(),E5,"d")&"天到期","已经过期")

mykitami

=IFERROR(TEXT(DATEDIF(TODAY(),E5,"d"),"[>30];还有0天到期"),"已经过期")

fxgg72

mykitami 发表于 2025-11-3 18:14
=IFERROR(TEXT(DATEDIF(TODAY(),E5,"d"),"[>30];还有0天到期"),"已经过期")

这个公式如果在>30天时间只会显示数字，有一点点缺憾。
谢谢老师！

Ruby_碧碧

szl3178 发表于 2025-11-3 16:01
=TEXT(DATEDIF(D5,E5,"D"),"[>30] ;距离合同到期还有0天")

多了个；号，=TEXT(DATEDIF(D5,E5,"D"),"[>30]距离合同到期还有0天")

Ruby_碧碧

fxgg72 发表于 2025-11-4 06:12
这个公式如果在>30天时间只会显示数字，有一点点缺憾。
谢谢老师！

删掉分号就正确了