Home

Need to calculate minutes and seconds

%3CLINGO-SUB%20id%3D%22lingo-sub-858482%22%20slang%3D%22en-US%22%3ENeed%20to%20calculate%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858482%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20work%20out%20how%20to%20calculate%20minutes%20and%20seconds%20to%20help%20plan%20play%20lists%20for%20my%20radio%20programme%20but%20am%20unable%20to%20fathom%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20want%20to%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrack%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%3A38%20(3%20mins%2038%20secs)%3C%2FP%3E%3CP%3ETrack%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3A58%20(2%20mins%2058%20secs)%3C%2FP%3E%3CP%3ETrack%203%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B5%3A03%20(5%20mins%203%20secs)%3C%2FP%3E%3CP%3ETrack%204%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B10%3A15%20(10%20mins%2015%20secs)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CSTRONG%3ETOTAL%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B21%3A54%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20format%20the%20columns%20to%20get%20excel%20to%20show%20the%20correct%20minutes%20and%20seconds%20and%20then%20be%20able%20to%20calculate%20how%20long%20the%20tracks%20in%20total%20last%20as%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-858482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858532%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410182%22%20target%3D%22_blank%22%3E%40Kevin62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20original%20time%20value%20in%20a%20cell%3F%3C%2FP%3E%3CP%3EIs%20it%20%3CU%3E(3%20mins%2038%20secs)%3C%2FU%3E%20OR%20%3CU%3E3%20mins%2038%20secs%3C%2FU%3E%20OR%20%3CU%3E3%3A38%20(3%20mins%2038%20secs)%3C%2FU%3E%20OR%20%3CU%3E3%3A38%3C%2FU%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859954%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bthe%20time%20value%20I%20want%20in%20the%20cell%20is%20%3CSTRONG%3E3%3A38%3C%2FSTRONG%3E%20which%20will%20represent%20the%20time%20of%203%20minutes%20and%2038%20seconds.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859964%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410182%22%20target%3D%22_blank%22%3E%40Kevin62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20always%20considers%20time%20in%20Hours%3AMinutes%3ASeconds%20and%20you%20can't%20change%20that.%20But%20you%20may%20interpret%20hours%20as%20minutes%20and%20minutes%20as%20seconds.%20One%20variant%20is%20apply%20custom%20format%20(Ctrl%2B1)%20on%20cell%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eh%3Amm%20%22(%22h%20%22mins%22%20mm%20%22secs)%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20it%20assumes%20your%20tracks%20are%20not%20more%20than%2024%20minutes.%20Another%20variant%20is%20to%20split%20on%20two%20columns%20and%20use%20elapsed%20time%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20830px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132224i5E4DD5081BB31CFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESame%20is%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860137%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410182%22%20target%3D%22_blank%22%3E%40Kevin62%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20you%20mean%20that%20if%20B2%20contains%20a%20string%20(3%20mins%2038%20secs)%20and%20you%20want%20to%20return%203%3A38%20from%20this%20string%2C%20then%20try%20this...%3C%2FP%3E%3CP%3EIn%20C2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTIMEVALUE(%220%3A%22%26amp%3BSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2%2C%22secs%22%2C%22%22)%2C%22mins%22%2C%22%3A%22)%2C%22(%22%2C%22%22)%2C%22)%22%2C%22%22)%2C%22%20%22%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20custom%20format%20the%20formula%20cell%20with%20%3CSTRONG%3Em%3Ass%3C%2FSTRONG%3E%20and%20copy%20the%20formula%20down.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132274i09E0BA6DC7B44FC5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22TimeStringToTime.jpg%22%20title%3D%22TimeStringToTime.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20find%20the%20attached%20with%20the%20formula%20implemented.%3C%2FP%3E%3CP%3EIf%20this%20is%20not%20what%20you%20are%20trying%20to%20achieve%2C%20please%20upload%20a%20sample%20file%20with%20data%20in%20original%20format%20in%20one%20column%20and%20with%20desired%20output%20mocked%20up%20manually%20in%20another%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kevin62
New Contributor

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

4 Replies

@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?

@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

@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

image.png

Same is in attached file.

@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.

TimeStringToTime.jpg

 

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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies