How do I sum increments of minutes and seconds

[SandyMichalski]

I have an excel spreadsheet that has time entered in for minutes and
seconds; ie 7 minutes and 28 seconds is shown as 7.28. How do I sum a
column with increments of time.

example:
7.28 (7 minutes & 28 seconds)
2.38 (2 minutes & 38 seconds)
5.48 (5 minutes & 48 seconds)

Total time should result in 15.54 (15 minutes & 54 seconds), but if I do a
simple SUM, the results are 15.14. HELP!

 

[hansyt]

You seem to have entered the dates as decimal values. But remember a
minute has 60 seconds, not 100. So 0.28 mintes equals 60/100*28 = 16.8
seconds.

Enter the values as 7:28, 2:38 and 5:48 and it should work. However,
remember that adding up times can never display more than 24 hours!

Hans

 

[Bernard Liengme]

Hi Sandy:
The problem is the decimal point where a colon is needed (7:28 not 7.28)
1) Use Edit | Find and Replace to convert dot to colon;
then put 60 in a blank cell, copy this, select you range of times, use
Edit|Paste Special Divide; format cells with m:ss
or
2) use a new column with (assuming first time is in A2) formula
=TIME(0,INT(A2),MOD(A2,1)*100) and format with m:ss
or
3) use
=TIME(0,SUM(INT(A2:A4))+INT(SUM(MOD(A2:A4,1)*100)/60),MOD((SUM(MOD(A2:A4,1)*100)/60),1)*60)
entered as array formula with SHIFT+CTRL+ENTER

 

[vezerid]

Sandy,
try this:

=SUM(TIMEVALUE("0:"&LEFT(A1:A3,FIND(".",A1:A3)-1)&":"&MID(A1:A3,FIND(".",A1:A3)+1,LEN(A1:A3))))

This is an array formula, hence it should be committed with
Shift+Ctrl+Enter.

The problem with this formula is that you have to specify the range
A1:A3 exactly in several parts, and it will give you a #VALUE! error if
applied to a bigger range than you have data, b/c of the blank cells. A
sightly more complext alternative,

In a separate cell (say C3) enter the range that you want to sum. E.g.
C3 contains text A1:A3

Then you can use the following (again array) formula

=SUM(TIMEVALUE("0:"&LEFT(INDIRECT(C3),FIND(".",INDIRECT(C3))-1)&":"&MID(INDIRECT(C3),FIND(".",INDIRECT(C3))+1,LEN(INDIRECT(C3)))))

HTH
Kostis Vezerides

 

[SandyMichalski]

I think I followed your instructions correctly, however, I having problems.

First I replaced the decimal point with a colon in my data. My data begins
in cell A1 and ends in A3. I selected the blank cell A4. I typed in the
formula =TIME(0,SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1)*100)/60),
MOD((SUM(MOD(A1:A3,1)*100)/60),1)*60) into the formula bar and did
SHFT/CTRL/ENTER.

Then I receive an error "One of the Agruments is not valid for this
function".

I'm lost. Help!

 

[Bernard Liengme]

Send me your file (to my personal email - not the newsgroup)