在 Excel 中输入不带冒号的时间 (hhmmss) 并计算时差

Question

我想输入不带冒号的时间，hhmmss并计算与另一个单元格的时间差。例如，我在一个单元格中输入开始时间，在另一个单元格中输入结束时间。然后我需要计算时间差。但它必须包括小时、分钟和秒。

问题在于我想输入不带冒号的时间，但在单元格中看到冒号。为此，我使用自定义数字格式格式化了单元格00\:00\:00。

Answer 1

一个选项是使用单元格格式和 Excel时间功能。

将时间输入单元格（在我的示例中为 A2 和 B2）格式化为Text。预期格式始终为hhmmss，因此对于小时数为个位数的时间，请输入前导零。然后，您可以使用以下公式进行计算：

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

这将抓取最左边的 2 个字符作为“小时”，中间的 2 个字符作为“分钟”，最右边的 2 个字符作为“秒”，并将它们转换为 Excel 可识别的时间。然后，它将一个字符减去另一个字符，并显示结果，格式如下hhmmss：

编辑：看到要求与问题中指定的不太一样，我修改了公式以通过填充来考虑前导零：

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

可读性极差，但是现在用零填充值并使用最右边的 6，因此无论使用多少个零都应该有效。

我相信您实际上希望像hh:mm:ss本例一样对结果进行特殊格式化。

Answer 2

这是按照您想要输入的方式解码时间的另一种方法：

我将开始时间和结束时间保留为未格式化的数字，以使操作更加直观。在此示例中，您的开始时间是午夜或中午后 25 秒，您可以输入 000025。作为数字，它将被存储为 25，即使您的格式使其看起来像 00:00:25。

此方法根据小时和分钟在 100 的幂数中的位置来剥离它们。无论如何，秒始终是正确的两位数字。C2 中的公式为：

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

MOD 函数与 INT 函数相反。它返回除法后的余数。

Answer 3

我知道这是一个老帖子，但是如果（就像我一样）人们在无数的论坛中寻找解决方案，以节省输入时间表的时间，而不必手动输入“冒号”，而是让冒号可见，然后能够进行计算.....所有这些都无需使用 VBA 脚本、隐藏单元格和公式....等等....等等....这是我想到的解决方案 - 它似乎适用于前导零、24 小时制和跨越午夜的时间表（尽管如果您预计时间表跨越多个午夜则需要进行调整 - 在我看来这不是必要的）。

另外，我不需要“秒”，但如果您遵循以下概念，您应该能够很容易地扩展它：

自定义格式化您的开始时间和结束时间单元格为：

0#”:”##

这会将它们变成“文本单元格”，但会填充数字以始终显示四位数字（即使以零开头，对于美国朋友则为零）。

请注意，这将允许输入任何四位数字，使其看起来像时间格式。因此理论上可以输入 0768，结果变成时间 07:68，这当然不存在。因此

a) 创建数据验证列表，如下所示： - 在单独的工作表（选项卡）中，在单个列中，创建所有可接受值的列表。 在我的情况下，这是从 0500、0515、0530、0545.....0445 开始的，因为我只处理 15 分钟的增量。 如果您需要深入到秒（真的吗？），这会变得很长.....即 050001、050002.... 等等。 或者您可以编写另一个公式来创建此列表，然后将文本数据（仅粘贴值）复制到新列中以删除公式（我会这样做）。 - 选择所有这些“可接受值”的单元格，然后在工具栏的“公式”选项卡中选择“定义名称”。给它起一个名字，如“MonkeyTimeList”。 - 返回时间表工作表，选择所有涵盖开始和结束时间的单元格，然后转到工具栏的“数据”选项卡，选择“数据验证>数据验证>设置”，在“允许”部分从下拉列表中选择“列表”，在“来源”部分输入您定义的名称，前面加上“=”，因此在我的情况下为“=MonkeyTimeList”。单击“确定”后，选择“错误警报”选项卡，勾选“显示错误警报...”框，然后在“样式”下拉列表中选择“停止”。随意添加错误消息，如“Monkey Man 说你是个白痴，试着以正确的格式输入时间！”。然后单击“确定”。 -这可确保只有“可接受”列表中的数据才能作为时间输入。

b) 理论上，您可以使用条件格式来突出显示添加了不正确数据的单元格。这样可以避免使用单独的工作表来处理数据验证数据。我没有这样做过，这就是为什么我说理论上。这可能是一个不同的教程......

因此，现在您可以输入时间，而不必每次都寻找冒号和 Shift 键。

如果您确实希望单元格格式显示秒数，则自定义单元格格式将如下所示：

0#”:“##”:“##

所以......现在计算从开始时间到结束时间的小时数....再次，这仅基于分钟....但如果你能遵循逻辑，也可以扩展到秒。

在第三列（假设 A 列为开始时间，B 列为结束时间，C 列为总小时数）中输入公式：

=IF(VALUE(B2)>=VALUE(A2),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),24,VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)))

现在来解释一下...

“IF”函数区分 24 小时制时钟中的午夜前和午夜后的结束时间。如果午夜后，则在结束时间上增加 24 小时，以便继续计算“结束时间减去开始时间”。如果午夜前，则无需增加 24 小时。因此，出于计算目的，时间 0100 实际上被视为 2500，0200 被视为 2600，依此类推。注意：这假设班次运行时间不超过 23 小时 45 分钟。如果运行时间更长，也许您需要将日期列考虑在公式中……再次说明，我不需要它。

MID 函数适用于 TEXT（因此称为 TEXT 函数），并以指定的格式返回引用单元格中特定位置的数字。因此，“MID(TEXT(B2,"0000"),1,2)”查看单元格 B2，并始终以 4 位数字/字母的格式查看它，并返回从位置“1”开始的两位数字。在我的例子中，这代表整小时。另一个示例：“MID(TEXT(A2,"000000"),5,2)”将查看单元格 A2，并返回从位置“5”开始的两位数字，这可能是整秒。

我在所有这些前面放置了“VALUE”函数，将返回的文本转回数字，以便我们可以在计算中使用它。

因此，这使我们能够分别处理每个单元格中的小时、分钟和秒。小时的计算很简单，因为它们已经是整数了。然而，分钟实际上是小时的分数，或者 60 分钟的分数，因此，一旦您提取出代表“分钟”的数字，除以“60”即可得到一小时的“分数”。秒也一样，除以 3600（一小时内的秒数）即可得到秒的“一小时的分数”。

现在时间以带分数的数字表示，您可以根据需要添加或减去。在我的公式中，将给出开始时间和结束时间之间的总小时数。我使用公式将单元格格式化为 Custom> 00.00，然后允许我乘以小时费率等。

如果有人仍然真的想在他们的配方中加入第二个，并且遇到困难，请告诉我。如果我在工作中感到无聊，我可能会提供帮助....但第二个？？？真的吗？？？