Excel中的日期运算与提醒

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

到期日期计算

首先,自然是建立Excel表格,在输入签约日期和合同期后,用户就不需要输入到期日期,直接计算出到期日期就可以了。可在到期日期中输入“=DATE(YEAR(B2)+C2,MONTH(B2),DAY(B2)-1)”这一公式(图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能够自动进行提醒,以免在众多日期中不小心看漏了,从而造成损失。这里介绍变色法,即临近到期日时,到期日期会自动变色。

要实现这一目的,可以在开始菜单中找到并下拉“条件格式”菜单,选择“管理规则”后,再选择“新建规则”中的“使用公式确定要设置格式的单元格”(图2),随后在“为符合此公式的值设置格式中”输入公式”=AND($D2>TODAY(),$D2-TODAY()

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20190910A0LM3300?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券