从混合类型的列计算 SUM() 时间

从混合类型的列计算 SUM() 时间

请考虑下表:

+------------------+------+--------+
|     Total worked | Total|  Tasks |
+------------------+------+--------+
|             07:30| 09:20|Day 10  |
+-----+-----+------+------+--------+
|09:00|09:20| 00:20| Task1         |
+-----+-----+------+---------------+
|09:20|09:25|'00:05| Task2         |
+-----+-----+------+---------------+
|09:25|09:30|'00:05| Task3         |
+-----+-----+------+---------------+
|09:30|10:30|'01:00| Task4         |
+-----+-----+------+---------------+
|10:30|13:00| 02:30| Task5         |
+-----+-----+------+---------------+
|15:00|19:40| 04:40| Task6         |
+-----+-----+------+---------------+
|19:40|20:20|'00:40| Task7         |
+-----+-----+------+---------------+

(我真的很抱歉,但是我没法做得更好了)。

Total Worked是我一天的工作量。
这些值是通过 2 个公式计算的:

  • 忽略的时间(表中以 开头的时间'):
    =TEXT(Bxx-Axx+ STYLE("CustomDoNotCalc");"hh:mm" )
  • 计算时间:
    =Bxx-Axx

Total Worked用简单的 来计算=SUM(Cxx:Cxx)

Total手动引入的。

我希望它可以被计算而不是手动的。

我尝试了以下公式并得到以下结果:

  • =SUM(DATE.VALUE(Cxx:Cxx))- 错误#VALUE?#VALUE!
  • =SUM(DATE.VALUE(TEXT(Cxx:Cxx,'hh:mm')))- 错误#VALUE?
  • 还有一个,但我忘了是哪一个

我该怎么做才能计算Total时间?

答案1

如果没有辅助列,您可以使用数组公式

=SUM(C2:C8,(IF(ISERROR(TIMEVALUE(C2:C8)),0,TIMEVALUE(C2:C8))))

结果如下:

在此处输入图片描述

要在 C9 中输入数组公式,请输入上面的公式没有花括号 {},然后按CTRL+ SHIFT+ ENTER。OO Calc 将在花括号内显示公式,以表明它是一个数组公式。

答案2

我建议引入一个辅助列指示在计算“总工作量”时是否应考虑时间。这样,使用 SUM() 和SUMIF()

在此处输入图片描述

C 列(持续时间)很简单=Bx-Ax。“总工作量(C9)”是=SUM(C2:C8),“净工作量(C19)”是=SUMIF(D2:D8,"=1",C2:C8)。所有单元格的单元格格式是HH:MM。我添加了一些条件格式来突出显示识别为净工作时间的任务。

(使用 LibreOffice 创建,应该与 OpenOffice Calc 相同)

相关内容