0

I have time values above 24 hours in one column and need to calculate a sum, but it's tricky. I tried using this formula

=ARRAYFORMULA(TEXT(SUM(IFERROR(TIMEVALUE(K2:INDIRECT(CONCAT("K", ROW()-1))))), "[hh]:mm"))

the time values are in column K

Also I tried using Query function

=Query({A1:K,{"Duration";ArrayFormula(if(len(K2:K),(hour(K2:K)/24+minute(K2:K)/1440),))}},"Select Sum(Col12)",1)

Result is same for both formulas. Here's the problem. Each 24 hours of each time value resets to 0 when calculating. Let's say the values are 12:00, 12:00, 23:00 then the sum is 47:00 which is OK. but when the values are 12:00, 12:00, 24:00 then 24 turns to 0 and the sum is 24:00. If the values are 12:00, 12:00, 25:00 then then 25 turns to 1 and the sum is 25:00. Same is when the values are 12:00, 12:00 and 49:00 as 49 is 24+24+1 so it turns to 1:00

player0
  • 124,011
  • 12
  • 67
  • 124
Alex
  • 11
  • 1

1 Answers1

1

try:

=ARRAYFORMULA(TEXT(SUM(IFERROR(VALUE(TO_TEXT(INDIRECT("A1:A"&ROW()-1))))), "[h]:mm"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124