Pls Help Adding Time in mmm.ss format

Hi!

i would appreciate any help on this as I have spent a great deal of time on this. I have a whole set of excel sheets with the following information pertaining to audio files

No Filename Duration

The durations have all been entered as 1.29, 32.31, 102.11 etc with the number before the decimal point indicating the minutes and the number after the decimal point indicating seconds(all formatted as general, which means that where the seconds is 20, it will display without the zero, such as 11.2, 10.3 etc)

I need to total the duration column, with the final display also showing in this same format and for teh life of me have not been able to do so. had the files been formatted better, with the minutes and seconds in separate columns, it would have been much easier, but right now i need to work with what i have.

would appreciate any help on this and also if you could point me out a better solution for this in future

Regards,

Sid

Hi,

Simply format as Number with 2 decimal places. But I am afraid, you won't be able to do correct math operations on these numbers as 1.50 + 1.20 will result in 2.70 and not 3.10.

Method 1:
For this you will have to do either of the following:
Say you have the numbers 1.5 and 1.2 in cells A1 and A2

In B1, enter
=INT(A1)
and copy down to B2. These are minutes.

In C1 enter
=MOD(A1,1)
and copy down to C2. These are seconds

For total time, enter the formula:
=SUM(B1:B2)+INT(C3/0.6)+MOD(C3,0.6)
which will return 3.1

Method 2:
To represent the numbers in excel time format:
In B1 enter
=TIME(0,INT(A1),(A1-INT(A1))*100)
and copy down. Format as hh:mm:ss

To get the total enter
=SUM(D1:D2)
This will represent the time in hh:mm:ss
and so 102.10 is represented as 01:42:10


Mangesh

Mangesh,

thanks very much for your fast reply - I shall try this out and revert

Cheers

Sid

Thanks for the feedback. Will wait for the results.

Mangesh

Hi Mangesh,

Tried this out but i'm getting confused somewhere and the result is not coming out right! is there one formula I can use below the values to get the added result?

I will keep trying to figure out where I am going wrong.

Thanks for your help

Sid

Hi Sid,

can you tell me exactly what you did. Probably post the part which you think is giving incorrect results. The exact values you are using and the exact formulae.

Mangesh

I have modified the formula so that you need not separate the values into minutes and second. You can directly total them up. Suppose your values are in A1:A10, then use the following formula:
=SUM(INT(A1:A10))+INT(SUM(MOD(A1:A10,1))/0.6)+MOD(SUM(MOD(A1:A10,1)),0.6)

press control shift enter as this is an arrayformula

Mangesh

mangesh,

thanks very much and sorry for the late reply - this works perfectly thanks!

sid

As always, glad to recieve feedback

Mangesh