Hour, Minute, Second formatting in Pivot Table

[brianfitz]

Hi guys, hope you can help.

I have a sheet containing all of my pivot table data which is exported from our internal phone system. It contains stuff like handling time of calls, average call durations etc but the data is stored in seconds. For instance 8162 seconds is 6hrs 52 minutes. I get this figure by editing the cell formula to read =8162/60/60 and then changing the Cell Format to Custom and hh:mm:ss.

My question is I have over 2000 lines of this data, which increases by 2000 every month, and the above method of finding my hh:mm:ss is impractial to do as there dozens to do per line (meaning editing potenially 30,0000 individual cells.) is there a way that I can convert all of the time cells to this format and formula?

Sorry if I'm not being very clear but I appreciate any help you may be able to offer.

Many thanks,

Brian

 

[brianfitz]

Again, thanks for your help. The problem I have now is that every time I try to run the updated macro it says already converted. Please excuse my macro ignorance!

 

[Andrew Poulsom]

Start again with your original data.

 

[brianfitz]

Andrew all done now. Thanks so much for your help.