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...
excel
Formulas and Functions
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Sep 17, 2019

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?

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