返回目录:excel表格制作
日期和时间函数是Excel的一大特色,掌握日期和时间的用法可以帮助我们实现诸多高大上的功能,比如会员生日提醒、食品到期提醒、客户定期回访提醒等。
在学习日期和时间函数之前,我们需要先了解单元格格式设置、日期和时间序列号这两方面的相关用法。
1、日期和时间格式设置
日期和时间是一种相对特殊的数据类型,Excel提供了多种日期和时间的格式类型,可以通过快捷键Ctrl+1调出【设置单元格格式】来设置。
图1.1:设置日期格式
从调出的菜单中可以看到,Excel提供了丰富的日期和时间类型供用户选择,可以根据自己的需要设定相应的数据类型。菜单栏还提供了不同格式类型的示例,可以很方便地看到实际效果。
那么问题来了,如果不将单元格格式设置为日期或则时间类型,我们将看到什么?
图1.2:常规格式下的日期
上图我们将单元格数据类型设置为常规,回车后我们看到单元格内的日期变成了数字“43724”。
“What?---这是怎么回事???”。这个疑问将在要介绍的下一部分得到答案。
这里主要是提醒我们,日期和时间是一种较为特殊的数据类型,在处理这类数据的时候需要特别注意格式是否正确,否则在运用日期或时间函数进行计算时会遇到问题。
2、日期和时间序列号
先来看个案例:
图2.1:案例
可以看到,要得到昨天的日期,可以直接在今天的日期上减1;要得到明天的日期,就在今天的日期上加1。
从中我们可以看出,日期类型的数据也是可以像数值型数据一样直接进行加减运算。那么问题又来了,是什么神奇的力量使得日期类型的数据可以进行数学运算?
这个问题引出了我们下面要介绍的序列号概念。
Excel中,一个日期对应着一个唯一的序列号,整数1-2958465作为日期序列号,对应的日期为1990年1月1日-9999年12月31日。
表1:日期序列号
同样,每天的时间也对应着一个唯一的序列号,小数0-0.999988426作为时间序列号,对应的时间为一天的0点0分0秒-23点59分59秒。
表2:时间序列号
介绍到这里,真相就很清晰了:日期的加减计算,实际上是对序列号进行加减,本质上还是数值之间的运算。
这里我们会惊叹,用整数和小数作为序列号来表示日期和时间是多么聪明的创造啊!设计者的聪明之处还不止于此,我们还可以将上面的整数和小数组合起来使用,组合起来的数字对应的就是某个日期的具体时间。
表3:日期和时间序列号
也就意味着,某个日期某个时点都有一个唯一的由整数和小数组成的序列号,这个序列号可以像数值型数据一样做加法和减法,这就为日期和时间函数的出现奠定了基础。
3、常用日期和时间函数
1)TODAY函数
TODAY函数的语法格式为:
TODAY()
函数功能:返回电脑系统当前日期的序列号。如果在输入函数前,单元格的格式为“常规”,则结果将设为日期格式。
TODAY函数可以返回当前的日期,在需要计算日期间隔的情况下非常有用,比如食品保质期提醒、客户回访提醒及项目时长计算等,都需要用到这个函数。
下面演示下简单的项目已用时长计算过程:
图3.1:计算项目时长
C2单元格的公式为:TODAY()-B2,表示当前日期减去项目开始日期,得到项目到今天的已用天数。
需要注意的是,TODAY函数返回的时间是动态的,当你第二天重新打开表格时,你会发现,TODAY函数所在的日期已经更新为最新日期了。
2)NOW函数
NOW函数的语法格式为:
NOW()
函数功能:返回电脑系统当前日期及当前时间的序列号。如果在输入函数前,单元格的格式为“常规”,则结果将设为日期格式。
相比于TODAY函数,NOW函数除了可以得到当前日期外,还附带当前的时间点。
图3.2:NOW函数
同样,NOW函数得到的日期和时间也是动态的。如果你想设置为静态时间,只需要复制这个时间,选择性粘贴为数值。
图3.3:转化为静态时间
3)DATE函数
DATE函数的语法格式为:
DATE(year,month,day)
函数功能:返回代表特定日期的序列号。如果在输入函数前,单元格的格式为“常规”,则结果将设为日期格式。
参数year为1900-9999中的某一4位整数;参数month为1-12的整数,如果输入的月份大于12,则系统会自动往上调整年份(比如输入DATE(2019,15,21),则返回2020年3月21日);参数day为1-31的整数,如果输入的数字大于该月的最大天数,则系统会自动往上调整月份。
DATE函数在年、月、日为变量的情况下极为有用。比如业务部门为了方便汇总不同时期的销售业绩,经常将年、月、日分成3栏来输入,我们可以借助DATE函数实现组合。
图3.4:DATE函数的应用
4)YEAR/MONTH/DAY函数
YEAR函数的语法格式为:
YEAR(serial_number)
函数功能:返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。
MONTH函数的语法格式为:
MONTH(serial_number)
函数功能:返回某日期中的月份。返回值是 1到 12之间的整数
DAY函数的语法格式为:
DAY(serial_number)
函数功能:返回某日期的天数,返回值为1-31之间的整数。
以上三个函数其实是DATE函数里的3个参数,单独作为3个函数的目的就是为了得到DATE格式日期里对应的年份、月份及天数。有些情况下,它们的用法和DATE函数正好相反。
比如一线业务员提供给业务主管部门的销售数据中,年、月、日是组合在一起的,主管部门为了方便汇总不同时期的销售业绩,需要将年、月、日分成3栏,此时便可以借助YEAR、MONTH、DAY函数来实现。
图3.5:拆分日期
此外,函数HOUR、MINUTE用法类似,分别返回时间值的小时数和分钟数,这里就不再展开细述。
以上便是日期和时间函数较为完整的介绍了,在了解了日期和时间是借助于序列号来实现加减计算的机制后,相关的函数计算理解起来就轻松多了。
欢迎添加关注以接收后续精彩原创文章。
也欢迎转发此文分享给更多朋友!