Total duration an array of time

[minhhieule89]

Hi all, I have a spreadsheet like below

Start Time	End Time	Duration	A	B
8:22 AM	8:39 AM	16:47	0:17	17
8:54 AM	9:10 AM	15:53	0:16	16
9:33 AM	9:35 AM	2:03	0:02	2
9:40 AM	9:43 AM	2:46	0:03	3
10:05 AM	10:05 AM	0:13	0:00	0
10:07 AM	10:08 AM	1:01	0:01	1
10:36 AM	10:50 AM	13:30	0:14	14

<colgroup><col span="3"><col span="2"></colgroup><tbody>
</tbody>

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