在Excel 计算时间加减（时差、工作时间）

由于日期和时间在Excel 的后端存储为数字，因此您可以轻松地对日期和时间值使用简单的算术运算和公式。

例如，您可以加减两个不同的时间值或日期值，或者您可以计算两个给定日期/时间之间的时间差。

在本教程中，我将向您展示在 Excel 中使用时间执行计算的几种方法（例如计算时差、加减时间、以不同格式显示时间以及计算时间值的总和）。

Excel 如何处理日期和时间？

正如我所提到的，日期和时间以数字形式存储在Excel 的单元格中。整数代表一整天，数字的小数部分代表一天中的部分（可以转换为小时、分钟和秒值）

例如，值1 在Excel 中表示1900 年1 月1 日，这是Excel 开始考虑日期的起点。

因此，2 表示1990 年1 月2 日，3 表示1900 年1 月3 日，依此类推，44197 表示2021 年1 月1 日。

注意：Excel for Windows 和Excel for Mac 遵循不同的开始日期。Excel for Windows 中的1 表示1900 年1 月1 日，Excel for Mac 中的1 表示1904 年1 月1 日

如果这些数字中的小数点后有任何数字，Excel 会将这些数字视为一天的一部分，并且可以将其转换为小时、分钟和秒。

例如，44197.5 表示2021 年1 月1 日下午12:00:00。

因此，如果您在Excel 中使用时间值，则基本上是在使用数字的小数部分。

Excel 让您可以灵活地将小数部分转换为不同的格式，例如仅小时、仅分钟、仅秒或小时、分钟和秒的组合

既然您了解了Excel 中的时间是如何存储的，那么让我们看一些示例，了解如何在Excel 中计算两个不同日期或时间之间的时间差

计算两次时间差的公式

在许多情况下，您要做的就是找出两个时间值之间经过的总时间（例如，在具有In-time 和Out-time 的时间表的情况下）。

您选择的方法取决于单元格中时间的提及方式以及您想要的结果格式。

让我们看几个例子

Excel中计算时差的简单减法

由于时间在Excel 中存储为数字，因此找到2 个时间值之间的差异，您可以轻松地从结束时间中减去开始时间。

结束时间- 开始时间

减法的结果也将是一个十进制值，表示两个时间值之间经过的时间。

下面是一个示例，其中我有开始时间和结束时间，并且我通过简单的减法计算了时间差。

您的结果有可能以时间格式显示（而不是小数或小时/分钟值）。在上面的示例中，单元格C2 中的结果显示09:30 AM 而不是9.5。

这很好，因为Excel 会尝试从相邻列复制格式。

要将其转换为小数，请将单元格的格式更改为“常规”（该选项位于“数字”组的“主页”选项卡中）

获得结果后，您可以用不同的方式对其进行格式化。例如，您可以仅以小时或仅以分钟或小时、分钟和秒的组合显示值。

以下是您可以使用的不同格式：

格式	它能做什么
hh	仅显示两个日期之间经过的小时数
mm	以两位数显示小时数（例如04 或12）
hh：mm	显示两个日期之间经过的小时和分钟，例如10:20
HH：MM：SS	显示两个日期之间经过的小时、分钟和秒，例如10:20:36

如果您想知道在何处以及如何应用这些自定义日期格式，请按照以下步骤操作：

1. 选择要应用日期格式的单元格
2. 按住Control 键并按1 键（如果使用Mac，则按Command + 1）
3. 在打开的“单元格格式”对话框中，单击“数字”选项卡（如果尚未选择）
4. 在左侧窗格中，单击自定义
5. 在类型字段中输入任何所需的格式代码（在此示例中，我使用的是hh:mm:ss）
6. 点击确定

上述步骤将更改格式并根据格式向您显示值。

请注意，自定义数字格式不会更改单元格中的值。它只会改变值的显示方式。因此，我可以选择仅在单元格中显示小时值，而它仍然具有原始值。

专业提示：如果总小时数超过24 小时，请改用以下自定义数字格式：[hh]:mm:ss

以小时、分钟或秒为单位计算时差

当您减去时间值时，Excel 会返回一个十进制数，表示生成的时间差。

由于每个整数都代表一天，因此数字的小数部分将代表一天中的大部分时间，可以轻松转换为小时或分钟或秒。

以小时为单位计算时差

假设您有如下所示的数据集，并且您想要计算两个时间值之间的小时数

下面的公式将为您提供以小时为单位的时差：

=（B2-A2）* 24

上面的公式将为您提供两个时间值之间经过的总小时数。

有时，Excel 会尝试提供帮助，并且还会以时间格式为您提供结果（如下所示）。

您可以通过单击主页选项卡并在数字组中选择数字作为格式轻松地将其转换为数字格式。

如果您只想返回两次之间经过的总小时数（没有任何小数部分），请使用以下公式：

=INT((B2-A2)*24)

注意：此公式仅适用于两个时间值在同一天的情况。如果日期发生变化（其中一个时间值是另一个日期，第二个是另一个日期），这个公式会给出错误的结果。在本教程后面的日期更改时，请查看我介绍计算时差的公式的部分。

以分钟为单位计算时差

要以分钟为单位计算时差，您需要将结果值乘以一天中的总分钟数（即1440 或24*60）。

假设您有一个如下所示的数据集，并且您想要计算开始日期和结束日期之间经过的总分钟数。

以下是可以做到这一点的公式：

=(B2-A2)*24*60

以秒为单位计算时差

要计算以秒为单位的时间差，您需要将结果值乘以一天中的总秒数（即24*60*60 或86400）。

假设您有一个如下所示的数据集，并且您想要计算开始日期和结束日期之间经过的总秒数。

以下是可以做到这一点的公式：

=(B2-A2)*24*60*60

使用TEXT 函数计算时间差

另一种无需担心更改格式即可快速获取时差的简单方法是使用TEXT 函数。

TEXT 函数允许您在公式中指定格式。

=TEXT（结束日期- 开始日期，格式）

第一个参数是您要执行的计算，第二个参数是您要显示计算结果的格式。

假设您有一个如下所示的数据集，并且您想要计算两次之间的时间差。

这里有一些公式可以为您提供不同格式的结果

仅显示小时数：

=TEXT(B2-A2,"hh")

上面的公式只会给你显示两个时间值之间经过的小时数的结果。如果您的结果是9 小时30 分钟，它仍将只显示9。

显示总分钟数

=TEXT(B2-A2,"[mm]")

显示总秒数

=TEXT(B2-A2,"[ss]")

显示小时和分钟

=TEXT(B2-A2,"[hh]:mm")

显示小时、分钟和秒

=TEXT(B2-A2,"hh:mm:ss")

如果您想知道格式（或mm 和[mm]）中hh 和[hh] 之间的区别是什么，当您使用方括号时，它会为您提供两个日期之间的总小时数，即使小时值大于24。因此，如果您减去两个相差超过24 小时的日期值，使用[hh] 将为您提供总小时数，而hh 只会为您提供结束日期当天经过的小时数.

获取一个单位（小时/分钟）的时差，忽略其他

如果您只想以小时数或分钟数或秒数计算两个时间值之间的时间差，则可以使用专用的HOUR、MINUTE 或SECOND 函数。

这些函数中的每一个都接受一个参数，即时间值，并返回指定的时间单位。

假设您有一个如下所示的数据集，并且您想要计算这两次之间经过的总小时数和秒数。

以下是执行此操作的公式：

计算两次之间经过的小时数

=HOUR(B2-A2)

根据时间值结果计算分钟（不包括已完成的小时数）

=MINUTE(B2-A2)

从时间值结果计算秒数（不包括完成的小时和分钟）

=SECOND(B2-A2)

使用这些HOURS、MINUTE 和SECOND 公式时需要了解的几件事：

• 结束时间和开始时间之间的差异不能为负数（日期更改时通常会出现这种情况）。在这种情况下，这些公式将返回#NUM！错误
• 这些公式仅使用结果时间值的时间部分（并忽略天部分）。因此，如果结束时间和开始时间的差是2 天、10 小时、32 分钟和44 秒，则HOUR 公式将给出10，MINUTE 公式将给出32，SECOND 公式将给出44

计算到现在经过的时间（从开始时间开始）

如果要计算开始时间和当前时间之间经过的总时间，可以使用NOW 公式而不是结束时间。

NOW 函数返回使用它的单元格中的当前日期和时间。它是不接受任何输入参数的函数之一。

因此，如果要计算开始时间和当前时间之间经过的总时间，可以使用以下公式：

=NOW() - 开始时间

下面是一个示例，其中我在A 列中有开始时间，在B 列中有到现在为止的时间。

如果开始日期和时间与当前时间之间的时间差超过24 小时，那么您可以格式化结果以显示日期和时间部分。

您可以使用以下TEXT 公式来做到这一点：

=TEXT(NOW()-A2,"dd hh:ss:mm")

您还可以通过更改单元格的自定义格式（如本教程前面所述）来实现相同的目的，以便它显示日期和时间部分。

如果您的开始时间只有时间部分，那么Excel 会将其视为1990 年1 月1 日的时间。

在这种情况下，如果您使用NOW 函数来计算到现在所经过的时间，它将给您错误的结果（因为结果值也将包含自1990 年1 月1 日以来经过的总天数）。

在这种情况下，您可以使用以下公式：

=NOW()- INT(NOW())-A2

上面的公式使用INT 函数从now 函数返回的值中删除天部分，然后用于计算时间差。

请注意，NOW 是一个volatile 函数，它会在工作表发生更改时更新，但不会实时更新

计算日期更改时的时间（在Excel 中计算并显示负时间）

如果您的结束时间晚于开始时间，那么到目前为止介绍的方法效果很好。

但是，当您的结束时间低于开始时间时，就会出现问题。当您填写时间表时，通常会发生这种情况，您只输入时间而不是整个日期和时间。

在这种情况下，如果您在一个夜班工作并且日期发生变化，那么您的结束时间可能会早于您的开始时间。

例如，如果您在晚上6:00 PM 开始工作，并在早上9:00 AM 完成工作并暂停。

如果您只使用时间值，那么从结束时间中减去开始时间将得到9 小时(9 – 18) 的负值。

Excel 无法处理负时间值（就此而言，人类也无法处理，除非您可以进行时间旅行）

在这种情况下，您需要一种方法来确定日期已更改，并应进行相应的计算。

值得庆幸的是，有一个非常简单的解决方法。

假设您有一个如下所示的数据集，其中我有开始时间和结束时间。

正如您会注意到的那样，有时开始时间是晚上，结束时间是早上（这表明这是一个通宵班，并且一天已经改变）。

如果我使用下面的公式来计算时间差，它会在结果为负值的单元格中显示井号（在下图中以黄色突出显示）。

= B2-A2

这是一个IF公式，时间差值是否为负，如果为负则返回正确的结果

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

虽然这在大多数情况下效果很好，但如果开始时间和结束时间相隔超过24 小时，它仍然不够用。例如，某人在第1 天上午9:00 登录并在第2 天上午11:00 退出。

由于这超过24 小时，因此无法知道此人是在2 小时后还是在26 小时后退出。

虽然解决此问题的最佳方法是确保条目包括日期和时间，但如果它只是您正在使用的时间，那么上述公式应该可以解决大部分问题（考虑到任何人都不太可能工作超过24 小时）

在Excel 中加减时间

到目前为止，我们已经看到了开始和结束时间的例子，我们需要找到时间差。

Excel 还允许您轻松地从现有日期和时间值中添加或减去固定时间值。

例如，假设您有一个排队任务列表，其中每个任务都需要指定的时间，并且您想知道每个任务何时结束。

在这种情况下，您可以轻松地将每个任务将花费的时间添加到开始时间，以了解预计任务将在什么时间完成。

由于Excel 将日期和时间值存储为数字，因此您必须确保尝试添加的时间符合Excel 已经遵循的格式。

例如，如果您在Excel 中将日期加1，它将为您提供下一个日期。这是因为1 在Excel 中代表一整天（等于24 小时）。

因此，如果您想将1 小时添加到现有时间值，您不能继续简单地向其添加1。您必须确保将该小时值转换为代表一小时的小数部分。添加分钟和秒也是如此。

使用时间功能

Excel 中的时间函数获取小时值、分钟值和秒值，并将其转换为表示该时间的十进制数。

例如，如果我想在现有时间上增加4 小时，我可以使用以下公式：

=开始时间+ TIME(4,0,0)

如果您知道要添加到现有时间的小时数、分钟数和秒数，并且只需使用TIME 函数而无需担心将时间正确转换为十进制值，这将非常有用。

另外，请注意，TIME 函数只会考虑您输入的小时、分钟和秒值的整数部分。例如，如果我在TIME 函数中使用5.5 小时，它只会增加5 小时并忽略小数部分。

另请注意，TIME 函数只能添加小于24 小时的值。如果你的小时值超过24，这会给你一个不正确的结果。

分钟和秒部分也是如此，函数将只考虑小于60 分和60 秒的值

就像我使用TIME 函数添加时间一样，您也可以减去时间。只需将上述公式中的+ 号更改为负号即可

使用基本算术

当时间功能易于使用时，它确实有一些限制（如上所述）。

如果你想要更多的控制，你可以使用我将在这里介绍的算术方法。

这个概念很简单– 将时间值转换为代表一天中部分的十进制值，然后您可以将其添加到Excel 中的任何时间值。

例如，如果您想将24 小时添加到现有时间值，您可以使用以下公式：

=开始时间+ 24/24

这只是意味着我要在现有时间值上增加一天。

现在采用相同的概念，假设您想将30 小时添加到时间值，您可以使用以下公式：

=开始时间+ 30/24

上面的公式做同样的事情，其中​​ (30/24) 的整数部分表示您要添加的时间的总天数，小数部分表示小时/分钟/秒

同样，如果您想要添加到时间值的特定分钟数，则可以使用以下公式：

=开始时间 +（添加分钟数）/24*60

如果您有要添加的秒数，则可以使用以下公式：

=开始时间+（添加分钟数）/24*60*60

虽然这种方法不像使用时间函数那样简单，但我发现它要好得多，因为它适用于所有情况并遵循相同的概念。与时间功能不同，您不必担心要添加的时间是小于24 小时还是大于24 小时

您也可以在减去时间的同时遵循相同的概念。只需在上面的公式中将+ 更改为负号

如何在Excel中求和时间

有时您可能希望快速将Excel 中的所有时间值相加。在Excel 中添加多个时间值非常简单（只需要一个简单的SUM 公式）

但是，当您在Excel 中添加时间时，您需要了解一些事项，特别是要向您显示结果的单元格格式。

让我们看一个例子。

下面我在B 列中有一个任务列表以及每个任务将花费的时间，我想快速添加这些时间并知道所有这些任务将花费的总时间。

在单元格B9 中，我使用了一个简单的SUM 公式来计算所有这些任务将花费的总时间，它给我的值为18:30（这意味着它需要18 小时20 分钟才能完成所有这些任务）

到目前为止一切都很好！

如何在Excel 中求和超过24 小时

现在看看当我将任务2 的完成时间从1 小时更改为10 小时时会发生什么。

结果现在显示03:20，这意味着完成所有这些任务需要3 小时20 分钟。

这是不正确的（显然）

这里的问题不是Excel 搞砸了。这里的问题是单元格的格式设置为只显示结果值的时间部分。

并且由于此处的结果值超过24 小时，Excel 决定将24 小时部分转换为一天，将其从显示给用户的值中删除，仅显示剩余的小时、分钟和秒。

幸运的是，这很容易解决。

您需要做的就是更改单元格格式以强制它显示小时数，即使它超过24 小时。

以下是您可以使用的一些格式：

格式	预期结果
[h]：毫米	28:30
[m]：ss	1710:00
d “D” hh:mm	第一天04:30
d “D” hh “Min” ss “Sec”	1 天04 分钟00 秒
d “Day” hh “Minute” ss “Seconds”	1 天04 分00 秒

您可以通过转到格式单元格对话框并应用自定义格式来更改格式，或者使用TEXT 函数并在公式本身中使用上述任何格式

您可以使用下面的TEXT 公式来显示时间，即使它超过24 小时：

=TEXT(SUM(B2:B7),"[h]:mm:ss")

或者如果要将超过24 小时的小时数转换为天数，请使用以下公式：

=TEXT(SUM(B2:B7),"dd hh:mm:ss")

结果显示哈希(###) 而不是日期/时间（原因+ 修复）

在某些情况下，您可能会发现Excel 没有显示时间值，而是在单元格中显示井号。

以下是一些可能的原因以及解决这些问题的方法：

列不够宽

当一个单元格没有足够的空间来显示完整的日期时，它可能会显示井号。

它有一个简单的解决方法——更改列宽并使其更宽。

负日期值

Excel 中的日期或时间值不能为负数。如果您正在计算时差并且结果为负数，Excel 将显示哈希符号。

修复方法以更改公式以提供正确的结果。例如，如果您正在计算两次之间的时间差，并且日期发生变化，则需要调整公式以将其考虑在内。

在其他情况下，您可以使用ABS 函数将负时间值转换为正数，以便正确显示。或者，您也可以使用IF 公式来检查结果是否为负值并返回更有意义的值。

在本教程中，我介绍了有关在Excel 中计算时间的主题（您可以在其中计算时间差、加减时间、以不同格式显示时间以及求和时间值）

我希望您发现本教程很有用。

在Excel 计算时间加减的常见问题