Calculate the SUM() time from columns with mixed types

Ismael Miguel Published at Dev

Ismael Miguel

Consider the following table:

+------------------+------+--------+
|     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         |
+-----+-----+------+---------------+

(I'm really sorry, but couldn't do better).

The Total Worked is how much I've worked in a single day.
The values are calculated with 2 formules:

Ignored times (in the table, times starting with '):
=TEXT(Bxx-Axx+ STYLE("CustomDoNotCalc");"hh:mm" )
Calculated times:
=Bxx-Axx

The Total Worked is calculated with a simple =SUM(Cxx:Cxx).

The Total is introduced manually.

I want it to be calculated instead of being manual.

I've tried the following formules with the following results:

=SUM(DATE.VALUE(Cxx:Cxx)) - Error #VALUE? and #VALUE!
=SUM(DATE.VALUE(TEXT(Cxx:Cxx,'hh:mm'))) - Error #VALUE?
Another one, but I forgot which

What can I do to calculate the Total time?

tohuwawohu

Without a helper column, you may use a array formula:

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

The result looks as follows:

enter image description here

To enter the array formula in C9, enter the formula above without the curly braces {}, and press CTRL+SHIFT+ENTER. OO Calc will display the formula inside curly braces to indicate it's an array formula.

Collected from the Internet

Please contact [email protected] to delete if infringement.