Total duration an array of time

[minhhieule89]

Hi all, I have a spreadsheet like below

[TABLE="width: 392"]
<colgroup><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]Start Time[/TD]
[TD="align: right"]End Time[/TD]
[TD="align: right"]Duration[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8:22 AM[/TD]
[TD="align: right"]8:39 AM[/TD]
[TD="align: right"]16:47[/TD]
[TD="align: right"]0:17[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]8:54 AM[/TD]
[TD="align: right"]9:10 AM[/TD]
[TD="align: right"]15:53[/TD]
[TD="align: right"]0:16[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]9:33 AM[/TD]
[TD="align: right"]9:35 AM[/TD]
[TD="align: right"]2:03[/TD]
[TD="align: right"]0:02[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9:40 AM[/TD]
[TD="align: right"]9:43 AM[/TD]
[TD="align: right"]2:46[/TD]
[TD="align: right"]0:03[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10:05 AM[/TD]
[TD="align: right"]10:05 AM[/TD]
[TD="align: right"]0:13[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10:07 AM[/TD]
[TD="align: right"]10:08 AM[/TD]
[TD="align: right"]1:01[/TD]
[TD="align: right"]0:01[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10:36 AM[/TD]
[TD="align: right"]10:50 AM[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]0:14[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]

The Duration column is already calculated but I cannot use it since it's in the wrong format, when I sum the whole column I got 7:01 as the result which is not correct, the correct one should be 53 minutes

so I have to create helper column A which End Time minus Start Time, then helper column B which is taking HOUR(A)*60+MINUTE(A) to calculate in total how many minutes those event lasted

this is dump and I think there's must be a way to quickly calculate the 53 minutes, please help and thank you

 

[joeu2004]

the data was generated by a platform, they put mm:ss in the Duration column and looks like Excel recognize the data differently, so when I sum the Duration column it gives 13:00 as the result - the correct one is 53

As I explained, the sum only appears to be 13:00 because apparently, you formatted the cell as Custom m:ss .

But in the sample Excel file that you provide, "you" (or Excel on your behalf) format the cell as Custom h:mm , which displays 4:13.

As I said before, if you format the cell as Custom [h]:mm, you will see 52:13.

I'am look for one single formula that give me 53 minutes without having to have the helper columns

=ROUNDUP(SUM(F2:F8)*24, 0)

We multiply by 24 instead of 1440 because your data is actually hr:min , which you want to interpret as min:sec.

We round up because you want 52 min 13 sec to be converted to 53 min.

 

[joeu2004]

PS....

=ROUNDUP(SUM(F2:F8)*24, 0)

I used column F because it seems to be more accurate (interpreted as min:sec) than E2-C2 (hr:min).

But if you are content to recalculate duration using the times in columns C and E, use the following formula in F9, formatted as General or Number:

=ROUND(SUMPRODUCT(E2:E8 - C2:C8)*1440,0)

We multiply by 1440 because E-C is in the correct units of time, namely a fraction of a day, and there are 1440 minutes in a day.

We round in order to eliminate infinitesimal differences that arise in binary arithmetic with numbers with decimal fractions. Without rounding, note that =F9-53=0 returns FALSE, even though =F9=53 returns TRUE(!).

 

[minhhieule89]

PS....


I used column F because it seems to be more accurate (interpreted as min:sec) than E2-C2 (hr:min).

But if you are content to recalculate duration using the times in columns C and E, use the following formula in F9, formatted as General or Number:

=ROUND(SUMPRODUCT(E2:E8 - C2:C8)*1440,0)

We multiply by 1440 because E-C is in the correct units of time, namely a fraction of a day, and there are 1440 minutes in a day.

We round in order to eliminate infinitesimal differences that arise in binary arithmetic with numbers with decimal fractions. Without rounding, note that =F9-53=0 returns FALSE, even though =F9=53 returns TRUE(!).

perfect, thanks Joe, your formula works like expected