Excel中时间和日期的秘密

最近才发现,我们小区新来的保安竟然是哲学家!他经常在小区入口拦住我问了三个问题:“你是谁,你从那里来,你要到哪里去”?我一下子蒙住了,不知该如何做答,活了这么多年,我还真没有认真思考过这个问题,偶尔想到的只是:“到现在为止,我到底活了多少天?”如果没有Excel的存在,这个问题还真是一个问题!

有了Excel,这个问题非常容易!。假如某人的生日是1981年9月2日,在单元格A1里输入1981-9-2,在B1单元格里输入=Today(),在C1里输入=B1-A1,然后按下Enter键,C1单元格里立即得到这个人来到这个世界的天数,如图所示。


关于Excel日期和时间的问题,为什么两个时期相减就能得到它们相隔天数呢?原来Excel在本质上将日期和时间的存储为一个数字,默认情况下,Excel把1900-1-1 0:00:00存储为1,把1900-1-1 0:00:00以后的每一个时刻存储为该时刻与1900-1-1 0:00:00这个时刻的差值(以天为单位)。

不信?请在A列输入1900-1-1 0:00:00,单击鼠标右键,在弹出的快捷菜单里选择“设置单元格格式”命令,最后在弹出的“设置单元格格式”对话框中,把单元格格式设置为“数值”。你会发现,当把日期设置成数值格式后,A1单元格的内容变成了1。

由此看来Excel中真正的日期和时间本质上是以数值的形式存储在单元格里的,不管你把日期的格式设置成了什么样的“外在”显示形式。如图所示,我们把日期和时间2011-6-23 0:00设置成了不同的外在显示形式。


好了,既然在Excel中,日期和时间本质上是以天为单位的数值形式存储的,那么我们就可以直接对日期进行加减计算了。

假设A1单元格中内容为2011-6-23,那么再过100天是几月几日?在B1中输入“=A1+100”,结果是2011-10-1;那此日期前100天是几月几日?在B1中输入“=A1-100”,结果是2011-3-15。

而此日期前38小时又是什么时间呢?在B1输入公式“=A1-38/24”,结果是2011-6-21 10:00。因为38小时是38/24天。注意:在计算完成后,还需要把A1:B1单元格设置成能够显示日期和时间的格式如图所示。



真正的日期时间


工作中经常遇到这种情况:从公司业务数据库系统导出的数据看起来是日期时间,却不能在Excel函数公式中参与时间和日期相关的计算!

那是因为虽然单元格中的内容看起来像日期和时间,但实际上,它们在单元格中却是以文本的形式存储的(在默认情况下,真正的日期和时间应该是靠右对齐的,因为它们本质上是数字)。

判断一串字符是否为真正的日期和时间的方法是:选中这些单元格,看一看这些看似日期时间的内容是否能设置格式为“数值”格式,如果可以,那么这些内容就是真正的日期和时间,能够参与和日期时间相关的计算;否则,这些单元格内容的真正存储形式是文本,无法直接参与和日期时间相关的计算。

如图所示,“2011.6.23”在Excel中不是以日期的形式存储的,因为它不能设置格式为数值,无法参与和日期有关的计算!那么如何把这些内容转换成真正的日期呢?下面介绍的两种具体操作方法。


方法1:公式法

以下图中A列所示的“以文本形式存储的”日期数据为例,下面讲解如何把它们转化成能够参与日期相关计算的真正的日期。



使用公式法转换日期,我们必须首先了解,在当前的Excel环境中,真正日期的年、月、日之间是用什么符号作为分隔符的。假如在我们当前使用的Excel中,日期格式的年、月、日之间是用“-”符号分隔的。则我们可以用以下函数和公式把A列数据转化成真正日期:“=SUBSTITUTE(A1,".","-")*1”。其中函数SUBSTITUTE(A1,".","-")的含义是:用“-”字符替换文本中的“.”字符,把类似“2012.5.23”的数据变成了Excel的默认的日期形式“2012-5-23”;但需要注意,处理到这一步,数据的本质上还是文本,所以我们还需要在函数后面乘以1,把它转化成真正的代表日期的数字。

需要提醒的是,通过以上方式转化成的日期,有可能是以数字形式显示的,因此命令,作为最后一步,我们需要单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,把数字设置为日期格式。


方法2:使用“数据→数据工具→分列”命令


如果我们遇到的问题更加复杂,如图2-16所示:Excel导入的外部数据中的日期文本的年、月、日之间的分隔符有多种形式,并且月份和日期有时是两位数字,有时是一位数字,显然,这种情况用公式处理起来比较复杂。但通过仔细观察发现,不管分隔符是什么,所有日期都是按年、月、日的顺序排列的!在这种情况下,有一个处理该类问题的通用方法:“文本分列”法!

在下图中,A列中大部分内容是以文本类型存储的日期数据,其中还混杂着一些真正的日期(默认靠右对齐的数据)。下面我们用“文本分列法”批量地把这些数据转化为真正的日期。


首先选中A列,然后选择“数据→数据工具→分列”命令。在弹出的“文本分列向导——第1步,共3步”对话框中的“请选择最合适的文件类型:”处选择“分隔符号”单选框,然后单击“下一步”按钮如下图所示。




在“文本分列向导——第2步,共3步”对话框中,取消勾选所有分隔符号复选框,然后单击“下一步”按钮,如下图所示。


在“文本分列向导——第3步,共3步”对话框中的“列数据格式”选项中,选择“日期”单选框,并在日期选项后面的下拉列表中选择要处理数据的年、月、日的排列方式(Y代表年,M代表月,D代表日),然后单击“完成”按钮,如下图所示。




此时我们发现,原始数据已经按照我们期望转化成了真正的日期,从形式上看也已经全部变成了右对齐排列,如图所示。




从这个过程我们可以看到,Excel真的很聪明,只要告诉它“原始数据代表的是日期”和“年、月、日的排列顺序”,那么无论年、月、日之间用什么分隔符分开,Excel都能将其转化成真正的日期。现在我们就可以进行与日期相关的计算了。


DATEDIF()函数


谈到日期,就不能不提到在Excel中的一个隐藏的函数DATEDIF()。DATEDIF()用来计算两个日期之间的间隔的年、月、天的数量。比如某个人是在1971年9月2日出生的,那么到2012年1月1日,他一共活了多少天,多少个月,多少年呢?用DATEDIF()函数可以轻松解决这个问题,如下图所示。


DATEDIF()函数的用法是:DATEDIF(开始日期,结束日期,日期间隔单位)

其中“开始日期”和“结束日期”必须是Excel承认的真正的日期格式;“日期间隔单位”可以为:“Y”代表两个日期相隔的整年数;“M”代表两个日期相隔的整月数;“D”代表两个日期相隔的天数。

使用这个函数还需要注意:“结束日期”必须晚于“开始日期”,否则函数会报错,这大概和在英语里,描述时间开始和结束时习惯用“From…To…”有关吧。

非常令人奇怪的是,这么有用的函数在Excel帮助和Excel插入函数对话框里竟然找不到!至于Excel为什么把这么有用的函数隐藏起来,也许微软Excel项目开发组一定有自己的“难言之隐”吧!

更多内容请光临百度阅读作者林书明专区。百度阅读搜索_林书明

编辑于 2016-12-20

文章被以下专栏收录