Excel加班计算

Question

我需要计算我已经超过1小时/60分钟的小时数和分钟数。所以如果我做了1小时6分钟的事情，我在一个单元格(E9)中写下，我希望另一个单元格(G9)显示6分钟。因此，它显示我在所需的1小时内完成了6分钟。如果我做了2小时40分钟(E9)，我希望细胞(G9)显示1小时40分钟。

这有意义吗？我尝试让一个单元格显示实际工作时间(E9)，另一个单元格显示我超过标准60分钟/1小时的分钟和/或小时数。

请让我知道单元格应该有什么格式。我更愿意看到像(1: 40 )这样的东西来表示1小时40分钟，但是如果它更容易，比如100分钟，那也没问题。我只是不想要任何像1.38小时这样的东西。我需要知道真正的分钟数。

我还想让它显示，如果我在1小时以下，它会显示我在它下面的分钟数。因此，如果我只做40分钟(E9)，它将显示-20分钟(G9)

有人能帮我吗？

E9是存放我实际完成时间的单元格

G9应该可以容纳我超过一个小时的时间。

Answer 1

根据我的经验，Excel并不能很好地处理时间这个数量。它实际上只是从历史的实际角度来处理它。因此，您只需使用两个公式，这两个公式都将原始数字除以60，就可以得到您想要的结果：

  | Minutes Worked | Hours:Minutes Worked | Hours:Minutes Over 1 Hour
      100                 1:40                  0:40

  Column 1: Data entered by you, time worked in minutes
  Column 2: =QUOTIENT(A1,60)&":"&MOD(A1,60)
  Column 3: =QUOTIENT((A1-60),60)&":"&MOD(A1,60)

这些公式应该是有效的。第二列通过获取第一列中的值并将其除以60，然后向下舍入(FLOOR)以去掉余数，从而获得工作小时数。然后，它通过执行相反的操作来找到分钟。它接受输入的值并除以60，然后返回余数(MOD)。因为剩下的分钟数小时数总是0-59，所以你得到了你的分钟数。

第二列做了完全相同的事情，但它先从第一列中减去60 (1小时)。

它们都使用":“分隔小时和分钟的值，只需将其放在两个值之间。

如果您想要找到所有这些加班的总和，您只需要在第一列下面添加一个SUM公式，然后使用第二列和第三列中的完全相同的公式将其中一些转换为您想要的格式。

更新：

首先，我更改了上面的代码，将FLOOR替换为QUOTIENT，因为当数字小于1时，Floor会抛出错误。

其次，如果你想要一个负数，当你工作不到一小时时，你可以使用一个条件，当工作分钟数大于59时，使用公式，否则从60分钟数中减去60 (MOD)。我会用另一种方法来获得合适的负数，但当你工作40分钟时，它看起来就像00:-20，谁会想要这样？相反，我们在前面手动添加-00。

既然我们都这么做了，我想当你有0:30分钟或5:0小时以上的时间时，你需要"00“而不是"0”。因此，这可以通过将整个数字(小时数或分钟数，而不是整个单元格)包装在TEXT(number, "00")函数中来实现。最终的结果很复杂，但看起来像这样：

  Function for finding total hours:minutes from minutes
 =TEXT(QUOTIENT(A1,60),"00")&":"&TEXT(MOD(A1,60),"00")

  Function for finding hours:minutes (positive or negative) over first hour:
  =IF(A1 > 59,TEXT(QUOTIENT((A1-60),60),"00")&":"&TEXT(MOD(A1,60),"00"),"-00"&":"&TEXT(60-MOD(A1,60),"00"))

Answer 2

在几分钟(即100分钟)内完成此操作的一种快速方法是将内容写为G9：

= E9 - 60

如果您希望以小时和分钟显示时间，请根据需要键入以小时和分钟表示的时间，并确保将G9中的单元格格式设置为自定义，h:mm，然后将G9的值设置为：

=A1-1/24

由于Excel使用的是日期而不是时间，因此1/24等于一小时。

Answer 3

如果以下条件足以满足您的需求，请尝试：

三列(此处使用A、B、C)定义为：

A ["hh:mm"]  B [Standard]  C ["hh:mm"]

A：你的价值观

B：=IF(B1<1/24,"-","")

C：=ABS(B4-"1:00")

结果如下：

01:06     00:06
02:40     01:40
00:40   - 00:20

由于负数位于额外的单元格中，因此您不能直接使用这些值继续计算。