adding times in excel

[leehale]

I now this is an old one but I am having problems with it again. I have a long list of time totals that are formatted in gemeral instead of time. I need to total these times can you help please.
A
1 003:30:06
2 001:00:50
3 005:47:39
4 000:00:00
5 001:59:16
6 002:52:49
7 000:35:26
Total here

 

[AnotherHiggins]

So you always have an extra leading zero? If so, then the following formula will convert it to actual Excel-serial number times for you:
[COLOR=blue white]=VALUE(RIGHT(A1,LEN(A1)-1))[/color]
Format the column containing the formula to times.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[leehale]

Hi John.
I cannot take the first zero away because it is there to cope when the hours go above 99. Some of the totel colums go in to the hundreds so will need to add all of the numbers. Any other thoughts please.

 

[SkipVought]

Hi,

You do not have REAL TIME VALUES. You have TEXT data.

The CLUE is that your cells a formatted GENERAL. If they were REAL TIME VALUES, you would see NUMBERS.

You must CONVERT your string to TIME, using the TIME function and the MID function.

Assuning that your first value is in A2...
[tt]
=time(mid(A2,1,3),mid(A2,4,2),mid(6,2))
[/tt]
and copy down.

Skip,

_[red][/red]

 

[AnotherHiggins]

The reason you can't add the 'times' is because they are NOT times. Convert them to times and then you can add them together. If you want to display times that are greater than 24 hours (notice that the break point is 24, not 99 - that's because I'm talking about real, honest to goodness times), then format the cell/column as [color][hh]:mm:ss[/color]. The brackets tell Excel to display the total number of hours.

I'm curious: did you try following the directions in my first post before saying it won't work?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[smah]

What you actually have is elapsted time, not really a place in time. At the very least, you will need to use a colum to the right, then total that column. Use something like:

=LEFT(A1,3)+MID(A1,5,2)/60+RIGHT(A1,2)/3600

Then fill down & total. This will give you total elapsed time in hours.decimal - If you need hours:minutes:seconds, for the total, you'll need to convert back.

 

[smah]

Elapsed time is simply a number value that happens to be in a peculiar format.

 

[SkipVought]

oops...

neglected the colons...
[tt]
=time(mid(A2,1,3),mid(A2,5,2),mid(8,2))
[/tt]


Skip,

_[red][/red]

 

[leehale]

Thanks all so far but I have still got problems. I have tried all of the suggestions so far and still have a problem. I dont think I have explained it properly so with your patients I will try again.
The numbers are the result of a report that are dumped into excel, they are the summed total of call time minutes. They do come formatted as general. I have tried to format as suggested by using, Time, 37:30:55 and then total the column but it still returns a zero. The number samples I placed on the first post did not include a number greater than then 99 hours such as this 123:32:12
I realise that I need to get the format to stick but have not been able to grasp that part. The columns range from 40 numbers to 800 and there are 12 columns spread thoughout the sheet so inserting is not really an option. I hope this helps. sorry for seeming but probably means I am. I hope this explains my problem a little more detailed for you to be able to help.

 

[SkipVought]

Did you try the formula...
[tt]
=time(mid(A2,1,3),mid(A2,5,2),mid(8,2))
[/tt]
???

I know EXACTLY what you meant.

Skip,

_[red][/red]

 

[smah]

I'm having a little difficulty getting the hhh:mm:ss format back into 1 cell for the total, but the way I'm reading it, is this not what you want?