Need to calculate minutes and seconds

[Kevin62]

Hi,

I am trying to work out how to calculate minutes and seconds to help plan play lists for my radio programme but am unable to fathom it out.

Here is what I want to do:

Track 1           3:38 (3 mins 38 secs)

Track 2           2:58 (2 mins 58 secs)

Track 3           5:03 (5 mins 3 secs)

Track 4         10:15 (10 mins 15 secs)

    TOTAL     21:54

How do I format the columns to get excel to show the correct minutes and seconds and then be able to calculate how long the tracks in total last as above.

Thanks for your help

[#2]

@Kevin62 

What is the original time value in a cell?

Is it (3 mins 38 secs) OR 3 mins 38 secs OR 3:38 (3 mins 38 secs) OR 3:38?

[#3]

@Subodh_Tiwari_sktneer the time value I want in the cell is 3:38 which will represent the time of 3 minutes and 38 seconds.

Thanks

[#4]

@Kevin62 

Excel always considers time in Hours:Minutes:Seconds and you can't change that. But you may interpret hours as minutes and minutes as seconds. One variant is apply custom format (Ctrl+1) on cell as

h:mm "("h "mins" mm "secs)"

but it assumes your tracks are not more than 24 minutes. Another variant is to split on two columns and use elapsed time format

Same is in attached file.

[#5]

@Kevin62 

So if you mean that if B2 contains a string (3 mins 38 secs) and you want to return 3:38 from this string, then try this...

In C2

=TIMEVALUE("0:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"secs",""),"mins",":"),"(",""),")","")," ",""))

and then custom format the formula cell with m:ss and copy the formula down.

Also, find the attached with the formula implemented.

If this is not what you are trying to achieve, please upload a sample file with data in original format in one column and with desired output mocked up manually in another column.

[#6]

@Subodh_Tiwari_sktneer 

Hi Subodh 

This could probabky solve ur need