Pls Help Adding Time in mmm.ss format

  • Thread starter Thread starter skiddyrow
  • Start date Start date
S

skiddyrow

Hi!

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

No Filename Duration

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

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

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

Regards,

Si
 
Mangesh,

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

Cheers

Si
 
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 perfectl
thanks!

si
 
Back
Top