Adding Times

**james4ihl** · 02-20-2007, 09:03 AM

Hi --

I'm relatively new to Excel. I'm trying to add a column where I've typed in times in the format hh:mm:ss (e.g. 00:01:33).

Every time I try to SUM this, I get 00:00:00

I've tried formatting the cells to time, using various options [hh]:mm:ss, etc but no matter which times I try to add up, I always get zero!

Can you tell me what I'm doing wrong, please?

Many thanks,
James

**oldchippy** · 02-20-2007, 09:10 AM

Take a look at attached

**james4ihl** · 02-20-2007, 09:32 AM

Hi,

Thanks for that. I have it all formatted exactly like that, as far as I can tell, I'm still getting 0 for the sum. It's actually a list of hundreds of mobile phone call times in hhmmss that I need to add up to check my boss's bill, as there appears to be something amiss!

Sorry but I'm tearing my hair out here - according to everything I've read, and your suggestion, it should be working but it's not!

Cheers,
James

**oldchippy** · 02-20-2007, 09:39 AM

Can you provide a small zipped sample of your data. This will be easier for all of us to understand what you are trying to do, then hopefully a solution can be found.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this to your post.

**james4ihl** · 02-20-2007, 10:56 AM

Hi,

Yes, I'll have to save a copy with just the 'time' column so there's no personally identifyable data, and then I'll upload it for you...

Cheers,
James

**oldchippy** · 02-20-2007, 11:05 AM

OK post it and I'll have a look

**james4ihl** · 02-20-2007, 11:13 AM

Here's the data; I've left all formatting etc in tact for you. Very much hope you can shed some light on it. I'm now just simply frustrated that I can't get it to work!

Cheers,
James

**oldchippy** · 02-20-2007, 11:29 AM

The times were text with may be some additional characters in the cell, anyway think this has sorted it for you.

**james4ihl** · 02-20-2007, 11:40 AM

That's brilliant! Thank you so much!

Now, how did you change the format from text? I've been trying to do that all afternoon by going to format->cells->time (or custom)... how did you change it?

Also, since it's now working, is there a way to get a total in minutes, instead of hours and minutes?

Thanks again!

James

**oldchippy** · 02-20-2007, 11:56 AM

Originally Posted by james4ihl

That's brilliant! Thank you so much!

Now, how did you change the format from text? I've been trying to do that all afternoon by going to format->cells->time (or custom)... how did you change it?

Also, since it's now working, is there a way to get a total in minutes, instead of hours and minutes?

Thanks again!

James

First of in the formula the TRIM(E2) removes any other spaces or characters that may be in the cell, which can cause the cell to default to text.

The TIMEVALUE(TRIM(E2) then converts it to a numbered time value

The ISERROR part is for, if the cell is blank and error is returned TRUE or FALSE

The IF(ISERROR part says if the cell is blank ie. TRUE, then leave blank, else display the time.

**oldchippy** · 02-20-2007, 12:05 PM

Sorry to get hour and minutes to total minutes, then

=1440*MOD(F293,1) where F293 is the time in hours & minutes

**james4ihl** · 02-20-2007, 12:25 PM

Originally Posted by oldchippy

First of in the formula the TRIM(E2) removes any other spaces or characters that may be in the cell, which can cause the cell to default to text.

The TIMEVALUE(TRIM(E2) then converts it to a numbered time value

The ISERROR part is for, if the cell is blank and error is returned TRUE or FALSE

The IF(ISERROR part says if the cell is blank ie. TRUE, then leave blank, else display the time.

OK, so how would I go about applying all that to the multiple cells of text data like you did? Highlight them all first? Sorry to be vague I'm just learning excel!

Cheers,
James

**james4ihl** · 02-20-2007, 12:26 PM

Originally Posted by oldchippy

Sorry to get hour and minutes to total minutes, then

=1440*MOD(F293,1) where F293 is the time in hours & minutes

I did a sneaky reformat in the total cell to [m]:ss, which gave me the same total in minutes, but great to know the other formula as well!

Cheers,
James

**oldchippy** · 02-20-2007, 12:34 PM

Originally Posted by james4ihl

OK, so how would I go about applying all that to the multiple cells of text data like you did? Highlight them all first? Sorry to be vague I'm just learning excel!

Cheers,
James

Just insert a extra column next to your "text" times, by right clicking on the column header and select insert. Then put the formula in the first cell next to your "text" times, then auto-fill down by double-clicking on the bottom right corner of the selected cell