Adding Time: Adding individual cells works, but SUM doesn't

**fredmeister** · 03-28-2008, 12:21 PM

I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

Excuse my continued ignorance, but does anyone have a solution?

**oldchippy** · 03-28-2008, 12:28 PM

Hi,

Try Custom formatting your SUM cell to [hh]:mm

**fredmeister** · 03-31-2008, 11:56 AM

Unfortunately, that only seems to change the sum result to 00:00 as opposed to 00:00:00.

**oldchippy** · 03-31-2008, 12:03 PM

OK,

Format to [hh]:mm:ss

**fredmeister** · 04-01-2008, 05:08 AM

Hi again,

Unfortunately, 00:00:00 is still the result of =SUM(L8658:L21155)

**oldchippy** · 04-01-2008, 05:38 AM

Works OK for me?

**fredmeister** · 04-02-2008, 10:52 AM

Still a problem.

When I enter some sample data in a new xls and do as you suggested in your xls file, OldChippy, it works. But if I copy and paste my real data into a new xls, it still doesnt work, even with the formatting.

**EdMac** · 04-02-2008, 11:32 AM

Have you tried paste special > Values

**fredmeister** · 04-11-2008, 08:29 AM

Yes. And 00:00:00 is stil the result. The problem persists.

**darkyam** · 04-11-2008, 08:45 AM

Could you post a workbook containing some of the offending cells?

**fredmeister** · 04-11-2008, 09:49 AM

Attached.

Word limit.

**darkyam** · 04-11-2008, 10:00 AM

Type 1 in a cell and copy the cell. Select all the times you want to add, Paste Special, select Multiply.
*Edit: Alternately, if this is something you'll be doing often, change the formula to =SUM((A1:A52)+0) and confirm with Ctrl+Shift+Enter.

**fredmeister** · 04-11-2008, 10:13 AM

Typed 10:10:10 in A1 -> Ctrl + C -> Selected cells A2:A4 -> Right click -> Paste Special -> Selected Multiply

Cells a2:a4 have the value 00:00:00

**darkyam** · 04-11-2008, 10:16 AM

I don't know if I made myself clear enough. I was suggesting that you type literally the number 1 in a cell, say B1, then copy B1, Paste Special, multiply.

**fredmeister** · 04-11-2008, 10:17 AM

As i paste the formula =SUM((A1:A52)+0) into A53, the cell shows #VALUE!

**fredmeister** · 04-11-2008, 10:19 AM

Originally Posted by darkyam

I don't know if I made myself clear enough. I was suggesting that you type literally the number 1 in a cell, say B1, then copy B1, Paste Special, multiply.

Paste where?

**darkyam** · 04-11-2008, 10:30 AM

First, my suggestions are either/or. If you do one, then you do not have to do the other. Both of them work, you just have to choose which one works for you.
The formula, as I stated before, has to be confirmed with Ctrl+Shift+Enter. After you type it in, you have to press this key combination to get the formula to calculate correctly. Curly brackets, { and }, will appear around the formula.
For the other suggestion, after putting 1 in B1 and copying the cell, you would highlight all the times in column A that you want to add, and then use Paste Special -> Multiply there.