Excel中的日期运算与提醒

2019-09-10 07:22
电脑报 2019年30期
关键词:单元格运算公式

在制作Excel表格时,日期是办公用户经常遇到的要素,同时日期还经常要参与运算,如签订合同、计算合同到期日、领取证书、要注意换证日等。同时,即便设置了到期日期,工作一忙起来,也容易因查看不仔细,而导致合同、证书过期,那问题可就大了,日期运算、到期提示等,那可是相当重要啊。下面就以实例来看看日期的运算与提醒方法吧。

到期日期计算

首先,自然是建立Excel表格,在输入签约日期和合同期后,用户就不需要输入到期日期,直接计算出到期日期就可以了。可在到期日期中输入“=DATE(YEAR(B2)+C2,MONTH(B2),DAY(B2)-1)”这一公式,这个公式很简单,即提取B2单元格中的年份、月份和日期,并直接与合同期累加就可以了。

小提示:如合同期是以年计算,那么,直接输入YEAR(B2)+C2就可以得到到期年份,相应地,如果是以月计算,那输入MONTH(B2)+C2就可以了,不用考虑进位问题,这一函数会自动进位。另外在日期计算中,一定要使用DAY(B2)-1,毕竟到期日是要提前一天的。

事情结束了吗?没有,因为合同的有效期并不都以年来计数,还经常用到月、天这样的日期单位,面对这种情况该怎么办?首先介绍一下字符段截取语句,就是LEFT或RIGHT,其基本格式为LEFT/RIGHT (引用单元格,截取位数),其中LEFT为从左端截取,RIGHT为从右端截取。而LEN函数则是获得字符串中长度,使用這两个函数,可以用RIGHT(C2,1)来获得合同期的单位,看是年还是日、月,用LEFT(C2,LEN(C2)-1)函数来获得日期长度,以便于计算。

剩下的,就要用IF函数来进行单位判定,这条函数有点长,为=IF(RIGHT(C4,1)=“年”,DATE(YEAR(B4)+LEFT(C4,LEN(C4)-1),MONTH(B4),DAY(B4)-1),IF(RIGHT(C4,1)=“月”,DATE(YEAR(B4),MONTH(B4)+LEFT(C4,LEN(C4)-1),DAY(B4)-1),IF(RIGHT(C4,1)=“日”,DATE(YEAR(B4),MONTH(B4),DAY(B4)+LEFT(C4,LEN(C4)-1)-1)))),其实这条函数很简单,就是说如果合同期单位是年,那么将截获的数字加在年上,如果是月,加在月上,如果是日则加在日上,这样在合同日期中不管输入的是年、月、日等任何单位,都可以获得正确的到期日期。

到期提醒设置

有了到期日期,最好有个到期提醒,即临近到期时,Excel能够自动进行提醒,以免在众多日期中不小心看漏了,从而造成损失。这里介绍变色法,即临近到期日时,到期日期会自动变色。

要实现这一目的,可以在开始菜单中找到并下拉“条件格式”菜单,选择“管理规则”后,再选择“新建规则”中的“使用公式确定要设置格式的单元格”,随后在“为符合此公式的值设置格式中”输入公式“=AND($D2>TODAY(),$D2-TODAY()<10)”,这一公式的作用就是对比当前日期和D2单元格日期,当发现D2日期在当前日期之后,且距离在10天内输出信号,此时用户可点击下方的“格式选项”,在“填充”中选择适当的颜色(一般为红色),这样一旦到期日期在10天之内,单元格就会变为红色。

当然,由于合同的规模不同,一些大规模合同要提前安排才能生产,这样就要求有一个到期日期的查看才更人性化。而这一倒计时的公式很简单,为=D2-TODAY()&“天到期”,即将合同到期日期减去当前日期,并加上“天到期”字样,就可以了。

猜你喜欢
单元格运算公式
组合数与组合数公式
排列数与排列数公式
合并单元格 公式巧录入
流水账分类统计巧实现
玩转方格
玩转方格
长算式的简便运算
加减运算符号的由来
“整式的乘法与因式分解”知识归纳
“两两三三”解决天体问题