Рассчитать время SUM() из столбцов со смешанными типами

Question

Рассмотрим следующую таблицу:

+------------------+------+--------+
|     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 время?

Answer 1

Я рекомендую ввести вспомогательный столбец, указывающий, следует ли учитывать время при расчете "Всего отработано". Таким образом, довольно просто рассчитать длительность с помощью SUM() и SUMIF():

Столбец C (Длительность) просто =Bx-Ax . «Всего обработано (C9)» =SUM(C2:C8) , а «Чисто обработано (C19)» =SUMIF(D2:D8,"=1",C2:C8) . Формат ячейки для всех ячеек - HH:MM . Я добавил условное форматирование, чтобы выделить задачи, распознаваемые по чистому рабочему времени.

(созданный с помощью LibreOffice, должен быть таким же, как и OpenOffice Calc)

Answer 2

Без вспомогательного столбца вы можете использовать формулу массива:

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

Результат выглядит следующим образом:

Чтобы ввести формулу массива в C9, введите приведенную выше формулу без фигурных скобок {} и нажмите сочетание клавиш CTRL+SHIFT+ENTER. OO Calc отобразит формулу внутри фигурных скобок, чтобы указать, что это формула массива.