Adding hours minutes and seconds

[Guest]

Hi - Help! This is probably really simple, but I cannot figure it out. I am
working with a spreadsheet that has three columns that I need to look like
this...

A B - Time C - Total Time
Text 03:05:28 03:05:28
Text 01:10:30 04:15:58

03:05:28 is 03 hours, 05 minutes, and 28 seconds.

How do I format the cells for column B so that I can just type in "030528"
and have it look like column B?

Also, what is the formula for column C to add the time up? Any suggestions?

Thanks!!!
Jessica

 

[RJL0323]

I was not too sure I followed what columns you have but think I can help
you out. If you want to enter something like 101050 into cell A1 and
have it appear like 10:10:50 in cell B and then have some kind of
adding function in C for the times in column B. Use something like the
following.


The formula for column B would be:
=IF(LEN(A1)=6,(LEFT(A1,2)&":"&RIGHT(LEFT(A1,4),2)&":"&RIGHT(A1,2)),(LEFT(A1,1)&":"&RIGHT(LEFT(A1,3),2)&":"&RIGHT(A1,2)))

The reason I added the part about the length is the default format for
A1 when you enter something like 032518 would be general and the
leading zero would be dropped, appearing 32518.

In column C you add times just by adding different cells together, ie
=sum(B1:B5). There are a couple of things you need to keep in mind, if
you want the time to appear hh:mm:ss then you can not add numbers that
exceed the normal time format, meaning you can not have hours higher
than 24, minutes or seconds higher than 60. So you could not have an
entry in A1 like 257568. Also change the cell format for the cell that
is summing the times to [h]:mm:ss.

Sorry for being so wordy, hopefully this helps.

Ray

 

[Bob Phillips]

See http://www.cpearson.com/excel/DateTimeEntry.htm

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[hansyt]

Even if you format the column as hh:mm:ss then entering 030528 will
still see this as a date.
You need to have two columns, one to enter the data in the form you
want to and a second one wich you format to automatically display this
as a time.

Suppose you enter the data in A1, use this formula in B1:

=TIME(VALUE(LEFT(TEXT(A1,"000000"),2)),VALUE(MID(TEXT(A1,"000000"),3,2)),VALUE(RIGHT(TEXT(A1,"000000"),2)))

To get a running total of column B use this in B1 and copy into B2 etc.

=SUM($B$1:B1)

But be careful, this will only display values upto 24 hours and then it
will start with 0 again!

Hans

 

[hansyt]

Sorry,

running total in column C of course!

Hans

 

[Guest]

Works great. Thank You Thank You Thank You!!!

Jessica