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

你的浏览器不受支持

你需要更新你的浏览器才能使用该网站。

更新到 Internet Explorer 的最新版本

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

 

有关 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
反馈
("