Excel 时间公式：在 Excel 中计算、设置格式并处理时间

Excel 处理数字很方便，但处理时间（依我看）则是另一回事。最让人困惑的一点（我会在下文讲到）是，Excel 将时间值存储并处理为天数的小数部分，如果您不了解这一点，可能会得到意想不到的结果。

在本教程中，我将带您从创建与设置时间格式开始，到解决常见问题，例如负持续时间与时间溢出。读完之后，您将能够为项目计划、薪资核算或其他工作建立更智能、更可靠的电子表格。

了解 Excel 的日期系统

我们需要从 Excel 的日期系统说起。Excel 将日期存储为连续的序列号，从 1900 年 1 月 1 日开始，用数值 1 表示。此后每一天序列号加 1。例如：

• 1900 年 1 月 1 日 = 1

• 1900 年 1 月 2 日 = 2

• 2023 年 12 月 31 日 = 45205

时间值则存储为一天的小数部分。例如：

• 上午 6:00 = 0.25（6 小时是 24 小时的一天的 25%）

• 中午 12:00 = 0.5

• 下午 6:00 = 0.75

将日期与时间结合，只需把两者相加即可。比如，下面的公式将日期与时间相加：

=A2 + TIME(10, 30, 0)技术支持

如果 A2 为 45205（2023 年 12 月 31 日），结果将是 45205.4375，表示 2023 年 12 月 31 日上午 10:30。

如何在 Excel 中创建时间值

我们来看两种最常见的创建有效时间值的方法：使用 TIME() 函数，以及用 TIMEVALUE() 将文本转换为时间。

使用 TIME() 函数

我之前提到过它。TIME() 函数可由小时、分钟、秒三个部分构建一个时间值。语法如下：

=TIME(hour, minute, second)技术支持

例如，要表示上午 10:30，您可以写：

=TIME(10, 30, 0)技术支持

这会创建一个表示上午 10:30 的标准 Excel 时间值，内部存储为 0.4375（因为 10 小时 30 分钟是 24 小时的一天的 43.75%）。

Excel 对您输入的数值很灵活。如果超过常规时间边界，Excel 会将超出的部分进位。例如：

=TIME(27, 0, 0)技术支持

这将返回凌晨 3:00，因为 27 小时等于 1 个整天（24 小时）加 3 小时。 类似地：

=TIME(10, 75, 0)技术支持

这会被解释为上午 11:15，因为 75 分钟会变成 1 小时 15 分钟。

这些边界情况很重要，尤其当您通过公式动态生成时间值时。

使用 TIMEVALUE() 函数

您可以使用 TIMEVALUE() 将文本转换为时间。因此，如果工作表中包含文本形式的时间，如“2:30 PM”或“14:45”，Excel 可能不会自动将其视为时间值。这时就需要用到 TIMEVALUE()。

=TIMEVALUE("2:30 PM")技术支持

这会将文本字符串转换为 Excel 时间值。之后您就可以像对待其他时间一样在计算中使用它。

值得注意的是 TIME() 与 TIMEVALUE() 的区别：

• TIME() 由独立的数字（小时、分钟、秒）构建时间

• TIMEVALUE() 将文本字符串转换为时间

两者都会返回 Excel 识别的有效时间值，这对于准确计算与格式设置至关重要。

如何计算时间差

当您在 Excel 中拥有有效的时间值后，最常见的任务之一就是计算两个时点之间经过了多久。无论是衡量一个班次的时长，还是计算完成一项任务所需的时间，Excel 都提供了多种方法来得到答案。

我们来看看最实用的方法，从简单相减到更进阶的小技巧。

直接相减两个时间

最直接的计算时间差的方法是用结束时间减去开始时间。

例如，如果单元格 A1 为上午 10:00，B1 为下午 1:30，公式如下：

=B1 - A1技术支持

结果将是 0.145833。乍看可能有些奇怪，但请记住，Excel 用一天的小数来表示时间。在此情况下，0.145833 等于 3 小时 30 分钟。

为了让结果更实用，通常您会希望将其转换为人类可读的格式。

将时间差转换为小时、分钟或秒

您可以将相减的结果相应放大，以小时、分钟或秒表示时间差。以下是将时间差转换为小时的方法：

=(B2 - A2) * 24技术支持

以下是将时间差转换为分钟的方法：

=(B2 - A2) * 1440技术支持

以下是将时间差转换为秒的方法：

=(B2 - A2) * 86400技术支持

这些公式之所以有效，是因为一天有 24 小时、1,440 分钟和 86,400 秒。如果时间差是三个半小时，按小时公式会得到 3.5，按分钟公式会得到 210，按秒公式会得到 12,600。

提取特定时间单位

如果您只想提取时间的一部分，例如只要小时或分钟，可以使用 HOUR()。HOUR() 会返回时间的小时数部分。

=HOUR(B2 - A2)技术支持

MINUTE() 返回分钟部分。

=MINUTE(B2 - A2)技术支持

SECOND() 返回秒数。

=SECOND(B2 - A2)技术支持

这些函数很有用，但会在 23 小时、59 分钟或 59 秒后重置。因此如果持续时间超过一天，它们不会反映总用时。在这种情况下，更好的做法是直接将差值按比例放大，或使用自定义格式，我们稍后会介绍。

相加超过 24 小时的时间

如果需要相加超过 24 小时的时长，直接使用 TIME() 不会得到预期结果。相反，您可以用一天的小数来进行运算。

如下是相加超过 24 小时的方法。

=A2 + (28/24)  ' Adds 28 hours技术支持

如下是相加超过 60 分钟的方法。

=A2 + (90/1440)  ' Adds 90 minutes技术支持

如下是相加超过 60 秒的方法。

=A2 + (3600/86400)  ' Adds 3600 seconds技术支持

Excel 将时间存储为小数，其中 1 天等于 1，因此用 24、1440 或 86400 去除可将数值调整为一天的一部分。

处理负时间值

默认情况下，如果您用较小的时间减去较大的时间（例如 10:00 AM - 2:00 PM），Excel 会返回错误或显示一串 ###。

要安全地处理负时间值，请使用 MOD() 函数。该函数会将负结果“回绕”为有效时间，从而避免负数。

例如，要安全地减去 3 小时：

=MOD(A2 - TIME(3, 0, 0), 1)技术支持

此公式可确保结果始终为正的时间值，避免不必要的错误或显示问题。

如何正确设置时间结果的格式

在 Excel 中进行时间计算时，经常会出现令人困惑的小数或意外结果，特别是在处理超过 24 小时的持续时间或负时间值时。合适的格式设置可以将原始输出转化为可读、友好的格式，更易于理解和展示。

使用基础时间格式

如果您希望将时间差显示为“3:30”而不是小数，请使用 TEXT() 函数。

=TEXT(B2 - A2, "h:mm")技术支持

这会将结果格式化为小时与分钟。请注意，输出为文本，虽然显示美观，但若需继续参与计算，必须先将其转换回数值。

应用自定义时间格式

Excel 的内置时间格式能有效处理标准时间值，但自定义格式能更灵活地显示持续时间、超长小时数或特定时间组成部分。

应用自定义时间格式，请按以下步骤操作：

1. 选中包含时间值的单元格。
2. 右键选择设置单元格格式。
3. 在数字选项卡中选择自定义。
4. 输入所需的格式代码。

以下是一些常见的自定义时间格式：

• [h]:mm：显示总小时与分钟，即使超过 24 小时。例如：36:45（36 小时 45 分钟）

• d "days" h:mm:ss：显示天、小时、分钟和秒。例如：1 days 12:30:00

• h:mm AM/PM：以 AM/PM 标记显示时间。例如：10:30 PM

• mm:ss：只显示分钟与秒。示例：45:15

自定义格式能让您以更直观的方式呈现时间数据，更适用于工时表或工作日志等场景。

显示友好的时间差

如果您希望将时间差显示为更易读的字符串，例如“2 天 4 小时 15 分钟”，可以在一个公式中组合多个函数。

这是一个实用的示例：

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"技术支持

在此公式中，INT() 提取完整天数，HOUR() 与 MINUTE() 则提取剩余的小时与分钟。

为避免输出中出现 0 值，您可以将这些计算嵌入 IF() 语句中。例如：

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"技术支持

该公式仅显示相关的单位，使输出更简洁、更易读。

必备的 Excel 时间函数

如您所见，Excel 包含一套强大的时间函数。为了更完整地覆盖，下面快速回顾最重要的 Excel 时间函数。

使用 TIME() 函数创建时间值

TIME() 通过小时、分钟、秒三个部分构建有效的时间值。当您需要以编程方式构建时间值时，此函数非常合适。

下面是一个很好的示例：

=TIME(14, 30, 0)  技术支持

上述公式将返回下午 2:30，这源于以下语法。

=TIME(hour, minute, second)技术支持

使用 TIMEVALUE() 将文本转换为时间

如果您有文本形式的时间（如 "2:45 PM"），请使用 TIMEVALUE() 将其转换为可参与计算的标准 Excel 时间值。

=TIMEVALUE("2:45 PM")技术支持

例如，这将返回 0.614583，它是一昼夜的小数：

=TIMEVALUE("14:45")技术支持

用 NOW() 获取当前日期与时间

NOW() 返回当前日期与时间的单一值。每当工作表重新计算时，它都会自动更新。

=NOW()技术支持

这是一个很好的例子：如果当前时间为下午 3:15，NOW() 可能返回 44710.63542，其中 44710 是日期，.63542 表示时间。

用 TODAY() 获取当前日期

如果您只需要不含时间的当前日期，请使用 TODAY()。该函数对于计算截止日期或数据“龄期”特别有用。

=TODAY()技术支持

用 HOUR()、MINUTE() 和 SECOND() 提取时间组成

这些函数可让您分离时间值的特定部分，适用于报表与条件逻辑。

HOUR() 返回时间值的小时部分（0-23）。

=HOUR(A2)技术支持

MINUTE() 返回分钟部分（0-59）。

=MINUTE(A2)技术支持

SECOND() 返回秒部分（0-59）。

=SECOND(A2)技术支持

用 TEXT() 设置时间格式

TEXT() 将时间值转换为特定格式，非常适合显示持续时间或创建自定义时间字符串。 语法：

=TEXT(A2, "h:mm:ss AM/PM")技术支持

用 TEXT() 处理负时间值

在相减时间值时，MOD() 可确保结果始终为正，避免负时间引发的错误。

=MOD(A2 - B2, 1)技术支持

用 DATE() 创建日期值

DATE() 允许您用年、月、日三个部分创建日期。结合 TIME()，可将日期与时间合并为单一值。

=DATE(year, month, day)技术支持

下面是一个很好的示例：

=DATE(2023, 12, 31) + TIME(14, 30, 0)技术支持

结果为 12/31/2023 2:30 PM。

用 NETWORKDAYS() 计算工作日

NETWORKDAYS() 计算两个日期之间的工作日天数，排除周末，并可选排除指定节假日。适用于计算项目工期或薪资周期。

=NETWORKDAYS(start_date, end_date, [holidays])技术支持

例如：

=NETWORKDAYS(A2, B2, C2:C5)技术支持

如果 A2 为 12/1/2023，B2 为 12/31/2023，结果将是 12 月的工作日天数，排除 C2:C5 范围内列出的假日。

用 WORKDAY.INTL() 自定义工作日与周末

WORKDAY.INTL() 是 WORKDAY() 的进阶版本，允许您自定义哪些日子为周末。

=WORKDAY.INTL(start_date, days, [weekend], [holidays])技术支持

例如：

=WORKDAY.INTL(A2, 10, "0110000")技术支持

此处 0110000 指定周二（0）与周三（1）为周末。该公式会计算 A2 之后第 10 个工作日的日期，排除周二与周三。

结语

在 Excel 中处理时间可能感觉复杂，但用对函数就会容易得多。

如果您希望进一步提升 Excel 技能，请查看我们的 Introduction to Excel 入门课程，打好基础；或通过 Data Analysis in Excel 课程练习处理真实世界数据。

若想进阶应用，Excel Fundamentals 技能路径提供引导式项目与练习，帮助您掌握时间计算等更多技能。