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

 

[Norie]

Re: [Challenge] Total duration an array of time

How are you calculating the duration?

It should be a simple formula like =B2-A2 and you would be able sum with a simple formula like SUM(C2:C8).

 

[joeu2004]

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

Apparently, the Duration column is formatted to display min:sec; perhaps Custom m:ss .

I don't see how you ever get 7:01.

But simply fix the format of the cell with the SUM formula. Change it to Custom [m]:ss.

(I would use that format for all cells in the Duration column.)

When I fix the format, the sum is 52:13. But "[m]" will display minutes greater than 59.

If you want the sum in decimal minutes, perhaps rounded up (to 53), you can write:

=ROUNDUP(SUM(C2:C8)*1440, 0)

formatted as General or Number.

We multiply by 1440 because Excel time is stored as a fraction of a day, and there are 1440 minutes in a day.

To convert the rounded-up decimal minutes back to Excel time, write:

=ROUNDUP(SUM(C2:C8)*1440, 0)/1440

formatted as Custom [m]:ss or [m] .

 

[minhhieule89]

Apparently, the Duration column is formatted to display min:sec; perhaps Custom m:ss .

I don't see how you ever get 7:01.

But simply fix the format of the cell with the SUM formula. Change it to Custom [m]:ss.

(I would use that format for all cells in the Duration column.)

When I fix the format, the sum is 52:13. But "[m]" will display minutes greater than 59.

If you want the sum in decimal minutes, perhaps rounded up (to 53), you can write:

=ROUNDUP(SUM(C2:C8)*1440, 0)

formatted as General or Number.

We multiply by 1440 because Excel time is stored as a fraction of a day, and there are 1440 minutes in a day.

To convert the rounded-up decimal minutes back to Excel time, write:

=ROUNDUP(SUM(C2:C8)*1440, 0)/1440

formatted as Custom [m]:ss or [m] .

what it displays is different from the value it stores

https://imgur.com/a/FjlLQTa

for some reason I cannot insert the image, sorry

 

[minhhieule89]

what it displays is different from the value it stores

https://imgur.com/a/FjlLQTa

for some reason I cannot insert the image, sorry

here is the image

 

[Norie]

Where are the values in the Duration column coming from?

 

[joeu2004]

for some reason I cannot insert the image

IMHO, it would be better to upload the Excel file to a file-sharing website (e.g. box.net/files) and post the share/public URL. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors, and just download.)

Some participants object because they cannot or will not download files. But the devil is in details that are difficult to see or imagine from an image.

That said....

what it displays is different from the value it stores

No, you are confusing how it appears in the Formula Bar (which we cannot control) with how it appears in the cell (which we control with formatting).

But the Formula Bar demonstrates that you entered (manually typed?) 16:47, which Excel interprets as 16 hr 47 min (which is the same as 4:47 PM).

Having made that mistake, when you sum the column, Excel will automatically format the cell as Custom h:mm , and the total will appear to be 4:13.

It appears that you compounded those mistakes by formatting the sum as Custom m:ss. So we see only 13:00 (13 min 0 sec).

The format should be Custom [h]:mm . The "[h]" displays more than 23 hours. In this case, it will display 52:13, which looks like the value that you should expect.

Nevertheless, Excel interprets that as 52 hr 13 min, not 52 min 13 sec. You should correct the data entry (below).

-----

I suspect that you intended to enter 16 min 47 sec. In fact, I had assumed that you entered the formula =E2-C2. Your times appear to be 8:22 AM and 8:39 AM, which differ by about 17 min.

(I suspect that you actually entered seconds with the times. For example, 8:22:13 and 8:39:00 differ by 16 min 47 sec. And for all we know, the times include dates. This is why it is so useful for us to have the Excel file, not just an image or data that you copy into your posting.)

-----

Okay, let's starting correcting this mess....

In F2, enter the formula =E2-C2 and format it as Custom [m]:ss . The "[m]" displays more than 60 minutes. Even though it seems unnecessary in F2, it is a "good practice" when formatting elapsed time.

Copy F2 into F3:F8.

In F9, enter the formula =SUM(F2:F8) and format it as Custom [m]:ss. You will see 52:13 again; but this time, Excel correctly interprets it as 52 min 13 sec.

-----

Now, is that what you want? Or do you want time rounded up to the minute?

If so, change F9 to the formula =ROUNDUP(SUM(F2:F8)*1440,0)/1440. That will display 53:00 (53 min 0 sec).

Or do you want decimal time rounded up to the minute?

If so, change F9 to the formula =ROUNDUP(SUM(F2:F8)*1440,0) and format it as General or Number. That will display 53.

-----

Does that resolve all of your problems? Or did I misunderstand your requirements?

If the latter, please elaborate. And in that case, please upload an example Excel file to a file-sharing.

 

[sandy666]

another option with PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [End Time] - [Start Time], type duration),
    #"Extracted Minutes" = Table.TransformColumns(#"Inserted Time Subtraction",{{"Subtraction", Duration.Minutes, Int64.Type}})
in
    #"Extracted Minutes"[/SIZE]

after that add Totals Row

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Start Time[/td][td=bgcolor:#5B9BD5]End Time[/td][td][/td][td=bgcolor:#70AD47]Start Time[/td][td=bgcolor:#70AD47]End Time[/td][td=bgcolor:#70AD47]Subtraction[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

8:22 AM​

[/td][td=bgcolor:#DDEBF7]

8:39 AM​

[/td][td][/td][td=bgcolor:#E2EFDA]

08:22:00​

[/td][td=bgcolor:#E2EFDA]

08:39:00​

[/td][td=bgcolor:#E2EFDA]

17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

8:54 AM​

[/td][td]

9:10 AM​

[/td][td][/td][td]

08:54:00​

[/td][td]

09:10:00​

[/td][td]

16​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9:33 AM​

[/td][td=bgcolor:#DDEBF7]

9:35 AM​

[/td][td][/td][td=bgcolor:#E2EFDA]

09:33:00​

[/td][td=bgcolor:#E2EFDA]

09:35:00​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

9:40 AM​

[/td][td]

9:43 AM​

[/td][td][/td][td]

09:40:00​

[/td][td]

09:43:00​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10:05 AM​

[/td][td=bgcolor:#DDEBF7]

10:05 AM​

[/td][td][/td][td=bgcolor:#E2EFDA]

10:05:00​

[/td][td=bgcolor:#E2EFDA]

10:05:00​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10:07 AM​

[/td][td]

10:08 AM​

[/td][td][/td][td]

10:07:00​

[/td][td]

10:08:00​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10:36 AM​

[/td][td=bgcolor:#DDEBF7]

10:50 AM​

[/td][td][/td][td=bgcolor:#E2EFDA]

10:36:00​

[/td][td=bgcolor:#E2EFDA]

10:50:00​

[/td][td=bgcolor:#E2EFDA]

14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Total[/td][td][/td][td]

53​

[/td][/tr]
[/table]

 

[joeu2004]

@minhhieule89: PS.... If you want to enter 16 min 47 sec, type 0:16:47 or 16:47.0 . Then format the cell as Custom [m]:ss .

 

[minhhieule89]

IMHO, it would be better to upload the Excel file to a file-sharing website (e.g. box.net/files) and post the share/public URL. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors, and just download.)

Some participants object because they cannot or will not download files. But the devil is in details that are difficult to see or imagine from an image.

That said....



No, you are confusing how it appears in the Formula Bar (which we cannot control) with how it appears in the cell (which we control with formatting).

But the Formula Bar demonstrates that you entered (manually typed?) 16:47, which Excel interprets as 16 hr 47 min (which is the same as 4:47 PM).

Having made that mistake, when you sum the column, Excel will automatically format the cell as Custom h:mm , and the total will appear to be 4:13.

It appears that you compounded those mistakes by formatting the sum as Custom m:ss. So we see only 13:00 (13 min 0 sec).

The format should be Custom [h]:mm . The "[h]" displays more than 23 hours. In this case, it will display 52:13, which looks like the value that you should expect.

Nevertheless, Excel interprets that as 52 hr 13 min, not 52 min 13 sec. You should correct the data entry (below).

-----

I suspect that you intended to enter 16 min 47 sec. In fact, I had assumed that you entered the formula =E2-C2. Your times appear to be 8:22 AM and 8:39 AM, which differ by about 17 min.

(I suspect that you actually entered seconds with the times. For example, 8:22:13 and 8:39:00 differ by 16 min 47 sec. And for all we know, the times include dates. This is why it is so useful for us to have the Excel file, not just an image or data that you copy into your posting.)

-----

Okay, let's starting correcting this mess....

In F2, enter the formula =E2-C2 and format it as Custom [m]:ss . The "[m]" displays more than 60 minutes. Even though it seems unnecessary in F2, it is a "good practice" when formatting elapsed time.

Copy F2 into F3:F8.

In F9, enter the formula =SUM(F2:F8) and format it as Custom [m]:ss. You will see 52:13 again; but this time, Excel correctly interprets it as 52 min 13 sec.

-----

Now, is that what you want? Or do you want time rounded up to the minute?

If so, change F9 to the formula =ROUNDUP(SUM(F2:F8)*1440,0)/1440. That will display 53:00 (53 min 0 sec).

Or do you want decimal time rounded up to the minute?

If so, change F9 to the formula =ROUNDUP(SUM(F2:F8)*1440,0) and format it as General or Number. That will display 53.

-----

Does that resolve all of your problems? Or did I misunderstand your requirements?

If the latter, please elaborate. And in that case, please upload an example Excel file to a file-sharing.

Thanks Joe for your comment and others too


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

the formula I put in column G is E-C and formula is colum I is MINUTE(G) then the sum of total minutes is 53 which is the result I'm looking for and is correct

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

https://drive.google.com/open?id=1ZeIIrgrbPRjRUZLosnDD3-yVl_xLkE_U

thank you everyone, appreciate your comments