Sep 17 2019 03:45 AM
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
Sep 17 2019 04:12 AM
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?
Sep 17 2019 03:15 PM
@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
Sep 17 2019 03:19 PM
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.
Sep 17 2019 05:26 PM
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.
Jul 14 2023 01:26 AM