Correct Time Sum, by Formulas. Help Please.

B

Bubu

In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.
 
B

Bob Phillips

Bubu,

I did =SUM(A1:A7) and got 42:53.81 (sic!).

You might want to format it as [mm]:ss.00 in case it goes over an hour.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JE McGimpsey

Perhaps I'm just confused. I'm not sure how you're even displaying

05:22:76

much less using it to calculate hundredths of seconds. For me, that
entry converts to 05:23:16.

If your times are really in minutes:seconds:hundredths, then you're
going to have to do some manipulation here:

First, if that's the case, your "correct result" should be 42:54:61, not
42:53:81.


Second, XL doesn't understand hundredths of seconds using the colon - it
interprets it as hh:mm:ss, where ss is base-60, not base-100. So you can
never have a result where the last two digits are 60 or above.

You could format your data as

Format/Cells/Number/Custom [mm]:ss.00

and reenter your data as:

A1: 7:56.07
A2: 5:22.76
....
A8: =SUM(A1:A7) ==> 42:54.61

and XL will display the (true) correct result.


If you want to keep using hh:mm:ss to stand for mm:ss:hundredths, you
can use this array-entered formula (CTRL-SHIFT-ENTER or CMD-RETURN):


=SUM(INT(A1:A7*1440),SECOND(A1:A7)/100)/86400

which returns 42:54.61 when formatted as above.
 
B

Bob Phillips

JE McGimpsey said:
Perhaps I'm just confused. I'm not sure how you're even displaying

05:22:76

much less using it to calculate hundredths of seconds. For me, that
entry converts to 05:23:16.
Click to expand...

It is 05:22.76 (format is mm:ss.00)
 
R

Ron Rosenfeld

In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.
Click to expand...

You could use the array formula:

=SUM(--("0:"&SUBSTITUTE(A3:A15,":",".",2)))

and format as [m]:ss.00

or if you really want colons as separators throughout


=SUBSTITUTE(TEXT(SUM(--("0:"&SUBSTITUTE(A3:A15,":",".",2))),"[m]:ss.00"),".",":")

In the latter case, the result will be text and not a number that Excel can
manipulate.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place brackets {...} around the formula.


--ron
 
J

JE McGimpsey

Ah, well, in that case I was confused.

But in that case, why was the OP's SUM() off? Still confused.
 
Top