第6篇 掀起你的盖头来——理解日期时间函数.pdf

第6 章 掀起你的盖头来——理解日期时间函数 日常工作中难免会接触日期与时间，如不理解日期与时间的实质，涉及到的计算会非常头痛。本章 从日期与时间的实质谈起，讲解一些最常用的日期时间函数及其应用，帮助读者提高处理日期时间数据 的工作效率。 6.1 认识日期与时间 不要把日期与时间想得太复杂，其实就是一个数字。日期是一个整数，时间是一个小数，当然还有 取值的范围。 6.1.1 日期与时间的实质 很多用户制表的习惯是在录入完成后设置数字显示的格式。一个错误的操作习惯是先全选工作表， 然后设置格式，看看这样做会发生什么。 1．数字与日期时间的显示可以相互转换 图6.1 的例子是在录入数据完成之后先全选工作表，然后设置了会计专用格式。 图6.1 显示为数字的日期 C 列原先录入的是日期，通过设置会计专用格式后转化成了数字。这是一个正向的例子，如果把常 规的数字格式改变成日期格式，结果又会如何呢？ 在本书第3 章中介绍 “数字72 变”时举过一个例子，一个数字设置成Excel 内置的10 多种不同的 单元格格式，显示结果各不相同，这些格式中就有日期格式和时间格式，如图6.2 所示。 图6.2 数字显示为日期及时间格式 图6.2 中A2 和A3 单元格中数字不同，但显示为日期格式时返回结果相同；而A4 单元格中一个整 数显示为时间格式时返回结果为0 。 通过上述2 个例子，可以发现日期与时间和数字有着密切的联系。日期像是一个数字的整数部分， 而时间则是数字的小数部分。 以上仅是推测，看一下微软帮助文件中的讲解。 2 ．Excel 中的日期与时间规则 在Excel 中默认使用1900 日期系统，以1900 年1 月1 日为系统默认的第1 天，这一天的日期序列 号为1，每增加一天日期的序列号加 1，如1900 年1 月2 日的日期的序列号为2，……其他依此类推。 系统中最大的日期是9999 年 12 月31 日 （序列号为2,958,466 ），在起止期间的日期都能与数字相互转 换，反之不然。 图6.3 将数字转换成日期格式 图6.3 是一个将数字转换成日期的例子，在0-2,958,466 之间的数字都能转换成日期格式，如超出这 个范围Excel 返回值就会报错。这个例子验证了日期是有范围的，不仅是一个整数，而且还是一个不超 过2,958,466 的正整数。 理解了日期之后，时间就简单了。每增加一天，日期的序列号增加1，一天有24 个小时，小时折算 到天是1/24 天，一小时有60 分钟，一分钟有60 秒，折算方法是继续除以60。 3 ．判断日期时间与文本 日期的特点是每个月的天数不一样，可能有28 、29、30、31 这4 种情况。如果搞错了当月的天数， 可能会写出一个错误的日期值。 图6.4 判断日期与文本 图6.4 中最后2 个日期是错误的，2012 年2 月份只有29 天。Excel 对于超出正常范围的日期仍会以 原录入格式显示，区别于规范的日期，超出范围的日期仅是一个文本，在单元格中靠左对齐显示。 职场点睛：在实际工作中如使用了错误的日期格式会给后续工作带来麻烦，在未设置格式对齐的情 况下，观察日期的对齐方式是最简单的判断真假日期的方法。 还有一种方法是使用LEN 函数求出字符的长度，目前日期序列号都是5 位数的数字 （比如2012 年 6 月30 日的日期序列号为41,090 ），使用LEN 函数返回值为5。例如，LEN 函数返回值不是5，说明 单元格中的日期实际上是一个文本，如上图中最后2 个“日期”的字符长度都是9 位，就不属于数字范 畴了。 4 ．转换日期时间与数值 如是标准的日期时间格式，其本身就是一个数字，可以直接参与数值运算。如是文本状态的日期或 者时间，转换成数字序列号的方法有如下几种：  通过分列的方法转换成数值（第3 章第2 节中有过介绍）；  参与一次数值运算的方式转换成日期时间序列号，比如原数值*1；  还可以通过Excel 内置的函数转换。 图6.5 使用函数转换文本日