Adding a series of duration times

Hi folks,
I'm trying to solve a problem which should be incredbly easy.
I want excel to add a column of duration times (expressed in hours, minutes and seconds) to give me the total duration time.

Times should be entered in column A and automatically converted to hours, minutes and seconds. E.g. entering 12233 should automatically convert to 1:22:33 to represent 1 hour, 22 minutes and 33 seconds. I then want all the duration times entered in column A to be added to give me a total duration time. I have tried all kinds of formatting option and excel keeps spitting dates back at me. I do not want dates or actual times of day to be referred to anywhere in the spreadsheet. The spreadsheet I need deals only with duration times.

Can someone please help.

Thanks in advance.

You can't do that with cell formatting alone. Are you happy to use some code, I could point you in the direction of a "change event" which would automatically convert the data as you enter it?

Otherwise you could convert with a formula in the next column, i.e.

If you enter data in A1 as 12233

Then this formula in B1 will convert it to 1:22:33

=TEXT(A1,"00\:00\:00")+0

You can then just sum column B. Make sure you format the total cell as [h]:mm:ss

Originally Posted by daddylonglegs

You can't do that with cell formatting alone. Are you happy to use some code, I could point you in the direction of a "change event" which would automatically convert the data as you enter it?

Sorry about the very late reply. I'm a bit of a beginner but I'm happy to learn how to use some code. Could you point me in the direction of the "change event" to do the automatic conversion?

Cheers

Originally Posted by daddylonglegs

You can't do that with cell formatting alone. Are you happy to use some code, I could point you in the direction of a "change event" which would automatically convert the data as you enter it?

Otherwise you could convert with a formula in the next column, i.e.

If you enter data in A1 as 12233

Then this formula in B1 will convert it to 1:22:33

=TEXT(A1,"00\:00\:00")+0

You can then just sum column B. Make sure you format the total cell as [h]:mm:ss

Hi Folks!
This is my first post, so I hope I'm doing it right
I was fascinated by the above conversion formula.
=TEXT(A1,"00\:00\:00")+0
What purpose do the forward slashes(\) serve and also why the final "+0"?
Thank you
Hope I've done this right.
shalom
Auto

The \ tells excel that the next character - the : should be treated as literal text, so without the +0 the formula

=TEXT(A1,"00\:00\:00")

will convert 123456 to a text string "12:34:56".....but that's still a text string so we want to convert it to the actual time. You can co-erce a text formatted number (or time, or date) to be an actual number by performing a mathematical operation on it, so we use one that doesn't change the value, e.g. +0 or *1 or preceding with --, i.e.

=--TEXT(A1,"00\:00\:00")

This converts to the number that represents the time 12:34:56, you then have to re-format the cell to show as a time

Thanks for that Daddylonglegs
A timely and informative answer.
The more I learn about Excel the more I realise that I know bu44er all.(preceded by -- Who'da thunk?)
Thanks again
Auto

This was a GREAT and easy to understand to a problem I've been struggling with all weekend. I joined to forum to ask a question but found this after a simple search. Thank God for you young guys who know this stuff inside and out. ronb.