Excel help

[Martin clk]

Does anyone have knowledge on how to create a sum that will calculate a column of time. the format is HH:MM:SS.

Basically I've exported my Carphone Warehouse bill and I want to be able to check the time totals within allowance, over allowance etc etc. I cant seem to get a sum function that will total the whole column for me?

Any help much appreciated.

Martin

 

[masqueraid]

If you just sum the column and change the format of the cell containing the answer to HH:MM:SS you should get just what you need

 

[wallingd]

The standard sum function without anything clever seems to do this. Can you supply an example of the dataset? Is the total going over 24 hrs? Jst entering the values as 01:23:45 autoformats then as hh:mm:ss.

 

[masqueraid]

Dave has a good point if you want to count more than 24 hours a good shortcut is to custom format the cell as "dd hh:mm:ss" the first two digits will represent 24hr multiples. That should cover most of your bills unless you've got a teenager

 

[Martin clk]

I've done that already and it's not happening, tried time format and custom format.

Would someone mind pm'ing me an email address and having a quick look for me if I send it over?

My fingers are crossed

 

[imadoofus]

It works for me. Format cells as time, enter some time periods, and sum them (into a cell also formatted as time)

PJ

 

[mobeyone]

I dont think this will work - is it a csv?

I remember exporting a file in work which resulted in the forumla being spliced up to basically rearrange the values in the cells to make it into that order/format. A striaght sum will not work either i think... try custom?

Let me have a look and see if i can download my o2 bill.

BTW, the bills are never accurate, nor are your minutes used , acrrued etc.

 

[masqueraid]

Martin, you have mail!

 

[blassberg]

Martin you have mail!

Durations were not values - created a duration value column and
summed that and cost.

Also added a datafilter and a month column in case you need to split by month or type of call

You can now select a category (eg month and / or national calls
from the drop down in the title cells and the subtotal function
will give you cost and time.

Also added a pivot table to show off.

CB

 

[Martin clk]

Thanks guys for all your help, much appreciated. I look forward to analysing my future bills - how sad!!

 

[wallingd]

Not sad at all - important to understand them and know where you're spending the most .