convert seconds to Hours:Minutes:Seconds & add these amo

[baillieston]

would any one be able to help me with the following.
I have a row of data which contains the amount of time in seconds.
e.g 120 or 3600 or 3662

I would like to show these as hours: Minutues: seconds
e.g 120 would be 00:02:00
e.g 3600 would be 01:00:00
e.g 3662 would be 01:01:02

And if possible how would I add these to ensure that the the correct amount showed.

e.g if I had a list of 60 and they all were 00:02:00

I would want to see 01:20:00 as a total and not 00:120:00

Hope I have explained this OK.

Thanks for any help. VBA or formula.

 

[Juan Pablo González]

Re: convert seconds to Hours:Minutes:Seconds & add these

Use this formula

=A1/(24*60*60)

and format as [hh]:mm:ss

Then, just use a

=SUM(TheRange)

Excel will take care of the hours, minutes and seconds for you.

 

[onlyadrafter]

Re: convert seconds to Hours:Minutes:Seconds & add these

Hello

=d1/24/60/60

format as hh:mm:ss

=sum(d1:d60)

gives 2:00:00

 

[baillieston]

Re: convert seconds to Hours:Minutes:Seconds & add these

Thank you both very much indeed.
Both solutions work a treat.