Totaling on hours problem

[56_kruiser]

I am sure this is simple for the folks who know what they are doing. But I'm stumped.

Attached is a simple spreadsheet that has columns of start/end times, and a column for the duration represented by the start/end times. I want to total the hours at the bottom of the table for the duration. But I'm not getting a correct total.



I also have a pivot table in it that I'm trying to list each category (tag) and total hours for each category. Of course, I'm not getting correct totals for that.

I'm thinking I need to format something different. Wondering if anyone here has any suggestions.

Spreadsheet attached.

[bobsone1]

What is the 105 in your total for?
I.e. in D317 you have =SUM(Table1 [ Duration]).
I tried =SUM(Table1[Duration]) and it looks like it works... Does a total of 539:11:00 (22 days, 12 hours and 11 minutes) sound correct?

[56_kruiser]

Originally Posted by bobsone1

What is the 105 in your total for?
I.e. in D317 you have =SUM(Table1 [ Duration]).
I tried =SUM(Table1[Duration]) and it looks like it works... Does a total of 539:11:00 (22 days, 12 hours and 11 minutes) sound correct?

Yes it sounds correct, but...

It's really wierd. That total is a total as added by Excel by checking "Total Row" in the design elements. So, Excel put the 105 there. I don't know what it is.

When I try to just type your suggestion in (while the Total Row is still checked in the Design tab), and it shows 0. If I turn off the Total Row in Design tab, and type in what you list, I get a circular reference error.

Here's a screen capture:



UPDATE

Also, I got to thinking maybe I was typing the formula withing the defined table range, so I moved to the next cell down, and when I type it in, it does not recognize 'duration', and gives me a "Formulat typed contains an error" message.

Not sure what to think.

[56_kruiser]

Ok. I just went in once more, made sure of my table range. Typed in the formula at the bottom of the duration column, and it shows 0:00:00.

[bobsone1]

I think I have solved it.....
Try changing D317 cell formatting to Number>Custom>[h]:mm:ss

To avoid potential problems in the future, I would change the whole D column to [h]:mm:ss thereby allowing for any hour counts that exceed 24.

As for the number Excel is loading into the formula (e.g. 105) have a look at

http://office.microsoft.com/en-nz/excel-help/subtotal-function-HP010062463.aspx



105 is a min function and 109 = sum etc


Hope this helps.

[gebobs]

Change the format of the total to [h]:mm:ss. The way you have it, hh:mm:ss, it displays only the fraction part of the day.

Making that format change, the total becomes 539:11:00. With the current format, 11:11:00 is displayed as it lops off 528 hours (22 days).

Edit: I defer to the previous answer above. For some reason, I missed it.

[56_kruiser]

Thanks everybody. That got me going. I was thinking it was in the formatting, but just couldn't figure out what it needed to be.

Much appreciated.