• 547K Members
• 2,988 Online
• 653K Conversations

Highlighted
New Contributor

# Need to calculate minutes and seconds

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.

4 Replies
Highlighted

# Re: Need to calculate minutes and seconds

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?

Highlighted

# Re: Need to calculate minutes and seconds

@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

Highlighted

# Re: Need to calculate minutes and seconds

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.

Highlighted

# Re: Need to calculate minutes and seconds

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.

Related Conversations
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies