Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
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 -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=506951 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
See http://www.cpearson.com/excel/DateTimeEntry.htm
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "WannaChevy03" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
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(T EXT(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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
Sorry,
running total in column C of course! Hans |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding hours minutes and seconds
Works great. Thank You Thank You Thank You!!!
Jessica |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CALCULATE HOURS WITH MINUTES AND SECONDS? | Excel Discussion (Misc queries) | |||
Adding minutes & seconds | Excel Discussion (Misc queries) | |||
Sum minutes and seconds to total hours | Excel Discussion (Misc queries) | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
convert seconds to minutes and seconds | Excel Worksheet Functions |