Forum Discussion

Kevin62's avatar
Kevin62
Copper Contributor
Sep 17, 2019

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.

 

Thanks for your help

excel
Formulas and Functions

5 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 17, 2019

    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.

    • Kevin62's avatar
      Kevin62
      Copper Contributor
      Sep 17, 2019

      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

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Sep 18, 2019

        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.

         

         

Resources