走过路过不要错过
Hello,大家好!今天和大家分享如何在Excel中设置日期提醒。
我们在工作中会遇到诸如,合同多久到期,应收账款是否到期等,各种到期提醒问题。今天就和大家分享如何处理到期问题的一个案例。
1
问题描述
如下图所示,各员工持有的证件到期日期不同。
要求
:1、以当天日期作为比较基准,如果证件已到期,在提示栏内显示“已过期”;如果证件在未来7天内到期,提示栏内显示“紧急提醒”;如果证件在未来30天内到期,提示栏内显示“即将到期”。
2、已到期,整行记录填充红色背景色;紧急提醒,整行记录填充黄色背景色;即将到期,整行记录填充绿色背景色。
期望得到的效果如下图所示:
2
操作步骤
1、在D2单元格输入公式:
=IF(C2
2、选中A2:D8单元格,单击【开始】-【条件格式】-【新建规则】,打开【新建格式规则】对话框。
(1)选择【使用公式确定要设置格式的单元格】;
(2)输入公式:=$C2
单击确定,可以看到已过期的记录,整行数据填充为红色。
接下来按照同样的方法,为7天内到期的记录填充黄色,为30天内到期的记录填充为绿色。
3、选中A2:D8单元格,单击【开始】-【条件格式】-【新建规则】,打开【新建格式规则】对话框。
(1)选择【使用公式确定要设置格式的单元格】;
(2)输入公式:=$C2
单击确定,得到的结果如下图所示。可以看到,不仅7天内到期的记录填充为黄色,已过期的记录也填充为黄色。
单击【开始】-【条件格式】-【管理规则】,打开如下图对话框。单击选中第1个规则(也就是填充黄色的规则),然后单击向下的小三角按钮。
这样原本排在第1位的规则,移动到下方,成为第2个规则,如下图所示:
单击确定后,可以发现已过期的记录显示为黄色,7天内到期的记录显示为绿色。
4、选中A2:D8单元格,单击【开始】-【条件格式】-【新建规则】,打开【新建格式规则】对话框。
(1)选择【使用公式确定要设置格式的单元格】;
(2)输入公式:=$C2
单击确定,得到的结果如下图所示。可以看到,不仅30天内到期的记录填充为绿色,已过期和7天内到期的记录也填充为绿色。
单击【开始】-【条件格式】-【管理规则】,打开如下图对话框。
单击选中第1个规则(也就是填充绿色的规则),然后单击向下的小三角按钮,一直到该规则移动到最下方。如下图所示:
单击确定,得到的结果如下图所示。可以看到,已过期的填充为红色;7天内到期的填充为黄色;30天内到期的填充为绿色。
3
原理解析
本例中,需要将各员工的证件到期日期与当天日期进行比较。
使用today()函数动态获取当天日期。
今天是2022/3/21,那么today()返回的日期就是2022/3/21。当日期变
化时,today()返回的当天日期会自动更新。
将证件到期日期与当天日期比较,如果证件到期日期在当天日期之前,也就是说证件到期日期小于当天日期,则提示“已过期”;
如果证件到期日期在未来7天内,则提示“紧急提醒”;
如果证件到期日期在未来30天内,则提示“即将到期”。
这是逻辑判断的情形,我们自然想到使用IF函数。
因为涉及多种情形,因此要多层嵌套IF函数。
对证件到期日期与当天日期进行比较后,根据不同的间隔天数,对整行数据填充不同的背景色。
我们会想到使用条件格式。
在条件格式新建规则对话框中,如果公式返回的值为True,则适用设置的格式。
在本例中,设置了3个条件格式规则。
当证件到期日期小于当天日期时,背景色填充为红色;
当证件到期日期在未来7天内时,背景色填充为黄色;
当证件到期日期为未来30天内时,背景色填充为绿色。
当设置这3个条件格式规则时,我们需要调整3个规则的优先顺序。在【条件格式规则管理器】对话框中,排在前面的规则优先级别较高,而排在后面的规则优先级别较低。如果我们把条件格式规则“=$C2
点个在看你最好看
|
有话要说...