你目前正处于脱机状态,正在等待 Internet 重新连接

如何使用 Excel 中的日期和时间

针对 Office 2003 的支持已终止

Microsoft 已于 2014 年 4 月 8 日终止了针对 Office 2003 的支持。该更改已影响到您的软件更新和安全选项。 了解这一措施对于您的含义以及如何继续保持受保护状态。

 

有关 Microsoft Office Excel 2007,请访问 Office Online Excel 2007 帮助和操作方法网页
概要
本文逐步介绍了如何使用 Microsoft Excel 中的内置日期和时间函数执行复杂的日期和时间计算。

Excel 将所有日期存储为整数,将所有时间存储为小数。有了此系统,Excel 可以像处理任何其他数字一样对日期和时间进行加、减或比较操作,而且所有的日期都可以使用此系统进行处理。

在此系统中,序数 1 代表 1/1/1900 12:00:00 a.m.。时间存储为 .0 到 .99999 之间的小数,其中 .0 为 00:00:00,.99999 为 23:59:59。日期整数和时间小数可以组合在一起,生成既有小数部分又有整数部分的数字。例如,数字 32331.06 代表的日期和时间为 7/7/1988 1:26:24 a.m.。

为了帮助您执行复杂的日期和时间计算,Excel 添加了许多内置的日期和时间函数。

TODAY() 函数和 NOW() 函数

TODAY 函数根据您的系统时钟返回今天日期的序数,不包括时间。NOW 函数返回今天日期的序数,包括时间。

如何根据序数对日期排序

在 Excel 中,日期是根据日期序数而不是显示的数字进行排序的。因此,当您在 Excel 中对日期排序时,产生的结果可能不是您所期望的。

例如,如果对以 mmmm 日期格式显示的一系列日期进行排序(以便只显示月份),月份并 按字母顺序排序。相反,日期将根据它们的基本日期序数排序。

如何根据序数比较日期

由于在对日期和时间进行比较时也使用序数,因此实际结果可能与您所期望的(根据显示的值)不同。

例如,当使用 NOW 函数将某一日期与当前日期比较时,如以下公式所示
=IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
即使当前日期为 10/1/92,该公式也返回 FALSE;仅当日期为 10/1/92 12:00:00 a.m. 时,它才返回 TRUE。如果您通过公式比较

两个日期,而且无需在结果中包括时间,则可以改用 TODAY 函数来避免这一问题:
=IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

如何使用日期公式

如何确定今天与未来某个日期之间相隔的天数

要确定现在与未来某个日期之间相隔的天数,请使用下面的公式
="mm/dd/yy"-NOW()
其中 "mm/dd/yy" 是未来的日期。使用“常规”格式可以设置包含该公式的单元格的格式。

如何确定两个日期之间相隔的天数、月数和年数

要计算两个日期(分别在单元格 A1 和 A2 中输入开始日期和结束日期)之间相隔的天数、月数和年数,请按照下列步骤操作:

1. 创建一个工作簿。
2. 单击空白工作簿
3. 在工作簿中键入以下数据:

A1:03/25/94A2:05/01/98
在单元格 D1 中键入以下公式:
=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months, "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"

注意:复制和粘贴该公式时,一定不能包含换行符。如果包含换行符,该公式将无效。

4. 如果已经正确地键入了该公式,则 D1 现在将显示:

4 years, 1 months, 6 days


为计算天数、月数和年数而拆分的附加公式
也可以将该公式拆分为以下三个部分,以分别计算天数、月数和年数。

注意复制和粘贴这些公式时,一定不能包含换行符,否则这些公式无效。
时间部分                     公式   ------------------------------------------------------------------------两个日期之间          =A2-DATE(YEAR(A2),MONTH(A2)-相隔的天数,          IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"days"忽略年数和月数   两个日期之间          =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2)相隔的月数,        <=MONTH(A1), DAY(A2)<DAY(A1)),11,忽略年数和天数          IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months"两个日期之间        =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<相隔的年数                MONTH(A1),AND(MONTH(A2)=MONTH(A1),DAY(A2)<DAY(A1))),1,0)&" years"
注意在上述公式中,&" days"、&" months" 和 &" years" 是可选的。这使得您可以按天数、月数和年数来区分结果。

如何确定两个日期之间相隔的工作日数

要计算两个日期(分别在单元格 A1 和 A2 中输入开始日期和结束日期)之间相隔的工作日数,请按照下列步骤操作:

1. 创建一个工作簿。
2. 单击空白工作簿
3. 在工作簿中键入以下数据:
A1:03/25/94 A2:05/01/98

4. 在单元格 D1 中,键入以下公式:

=NETWORKDAYS(A1,A2)"1071" 显示为单元格 D1 中的结果。

注意:如果看到结果 #NAME,请单击 Excel 2003 工具菜单上的加载项。单击以选中分析工具库复选框,然后,单击(如果系统提示您是否要安装)。在 Excel 2007 和 2010 中内置了分析工具库外接程序。

如何递增日期

要在某个日期上增加一定年数、月数或天数,请使用以下公式;其中,reference 是日期值或者包含日期的单元格引用,value1value2value3 是年、月、日各自的增量。
=DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)


例如,要在某个日期上增加一个月,公式为:
=DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,DAY(DATEVALUE("6/20/96")))

如何使用时间公式

如何计算经过的时间

当从一个单元格的内容中减去另一个单元格的内容,以便计算二者之间经过的时间时,结果将是一个表示经过的小时、分钟和秒数的序数。为了使该数值便于理解,可在包含该结果的单元格中使用 h:mm 时间格式。

在下面的示例中,如果单元格 C2 和 D2 包含公式 =B2-A2,并且单元格 C2 的格式为“常规”,则 C2 单元格中将显示一个小数(本例中为 0.53125,它是 12 小时 45 分钟的序数表示形式)。
A1:开始时间    B1:结束时间    C1:差      D1:差(常规)       (h:mm)A2:06:30:00 AM    B2:7:15 PM    C2:0.53125  D2: 12:45

如果从开始时间到结束时间要经过午夜,则必须将 24 小时的时差计算在内。可以通过增加数值 1 来解决此问题,1 代表一个 24 小时的周期。例如,您可能设置了以下表,并且考虑到时间将跨越午夜。
A1:开始时间    B1:结束时间    C1:差      D1:差(常规)       (h:mm)A2:7:45 PM       B2:10:30 AM    C2:0.614583333  D2: 14:45


要设置此表,请在单元格 C2 和 D2 中键入以下公式:
=B2-A2+IF(A2>B2,1)

如何累计大于 24 小时的小时数和分钟数

要想正确显示大于 24 小时的时间,可以使用 37:30:55 内置格式。要想改用自定义格式,必须用括号将格式的小时参数括起来,例如:
[h]:mm

如何将日期转换成对应的小数

要将序列化日期 (h:mm:ss) 转换为小数 (0.00),必须通过将序数转换为 24 小时制来将其转换为小数。按如下公式将时间乘以 24,即可完成转换:
=Time*24
其中,Time 是要从时间格式转换为小数的数值,该数值可以是单元格引用,也可以是 TIMEVALUE 函数中的数值字符串。

例如,如果单元格 A1 包含时间“4:30”,来表示 4 小时 30 分钟,则公式为:
=A1*24
结果为 4.5。

如果该单元格同时包含日期和时间,则应使用以下公式:
=(Time-INT(Time))*24
例如,如果单元格 A1 的内容为“6/20/96 4:30 AM”,则公式为:
=(A1-INT(A1))*24
结果同样是 4.5。

如何将小数转换成对应的日期

要将小数 (0.00) 转换为其对应的序列日期 (h:mm:ss),必须通过将序数转换为 24 小时制来将其转换为小数。按如下公式用时间除以 24,即可完成转换:
=Time/24
其中,Time 是要从小数转换为日期序数的数值,该数值可以是单元格引用,也可以是真实的数值。例如,要让单元格 A1 中的值 4.5 表示 4 小时 30 分钟,公式为:
=A1/24
结果为 4:30。

如何在 Microsoft Excel for Mac 和 Excel for Windows 之间传送文件

默认情况下,Excel for Mac 使用 1904 日期系统,Excel for Windows 使用 1900 日期系统。这意味着当您在 Excel for Mac 中键入序数 1 并将其格式设置为日期时,Excel 将其显示为 1/2/1904 12:00 a.m.而 Excel for Windows 将序数 1 显示为 1/1/1900 12:00 a.m.

如果将文件从 Excel for Mac 传送到 Excel for Windows,日期系统之间的这一差异应该不会导致出现问题,因为日期系统是分别存储在每个文件中的。但是,如果在从不同的平台中生成的、使用不同日期系统的文件之间进行复制和粘贴,则显示的日期可能会与其正确日期相差四年零一天。

若要在 Excel for Windows 中更改为 1904 年日期系统,请按照下列步骤操作:
  1. 在 Excel 2003 中的“工具”菜单上单击“选项”。在 Excel 2007 及更高版本中转到 Excel 选项
  2. 在 Excel 2003 中,单击“计算”选项卡,然后单击以选中“1904 年日期系统”复选框。在 Excel 2007 及更高版本中单击高级选项。向下滚动到计算此工作簿时:,并选中使用 1904 年日期系统复选框。
若要在 Excel for Mac 中更改为 1904 年日期系统,请按照下列步骤操作:
  1. 在“Excel”菜单上,单击“首选项”
  2. 公式和列表下选择计算。
  3. 工作簿选项下,单击以清除 1904 年日期系统复选框
参考
有关详细信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
214233在 Excel 中将文本或数字转换为非预期的数字格式
214386 Microsoft Excel 中的最大时间
241072 在 Excel 中查看公式时日期和时间显示为序数
264372 如何控制和理解 Excel 中“设置单元格格式”对话框中的各个设置
 reviewdocID XL2007 XL2010
属性

文章 ID:214094 - 上次审阅时间:11/29/2013 20:18:00 - 修订版本: 2.0

  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate KB214094
反馈