Converting a number of a format (2:16) into minutes and seconds

%3CLINGO-SUB%20id%3D%22lingo-sub-201738%22%20slang%3D%22en-US%22%3EConverting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201738%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20Dear%20Friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20been%20struggling%20for%20almost%20half%20a%20day%20(!)%20now%20to%20convert%20some%20number%20that%20my%20team%20sends%20me%2C%20in%20excel%2C%20which%20are%20in%20the%20format%20like%20%3CSTRONG%3E2%3A16%3C%2FSTRONG%3E%20or%20%3CSTRONG%3E1%3A07%3C%2FSTRONG%3E%20etc.%3C%2FLI%3E%3CLI%3EThese%20are%20the%20%3CSTRONG%3Eminutes%20%26amp%3B%20seconds%3C%2FSTRONG%3E%20recorded%20by%20them%20for%20a%20certain%20activity.%20The%20excel%20(sent%20by%20team)%20shows%20these%20number%20%2F%20cells%20formatted%20as%20'General'%20and%20no%20special%20formatting%20is%20applied%20by%20the%20team.%3C%2FLI%3E%3CLI%3EI%20now%20intent%20to%20convert%20this%20into%20%3CSTRONG%3Eseconds%3C%2FSTRONG%3E%20to%20find%20the%20%3CSTRONG%3Eaverage%3C%2FSTRONG%3E%20etc.%3C%2FLI%3E%3CLI%3EI%20tried%20several%20options%20like%20'Custom%20formatting'%20to%20'%5Bss%5D'%20etc.%20but%20none%20seems%20to%20be%20working.%3C%2FLI%3E%3CLI%3EThe%20only%20way%20it%20worked%20till%20now%20is%20when%20I%20%3CSTRONG%3Eadd%20%2200%3A%22%20manually%3C%2FSTRONG%3E%20before%20each%20number.%20i.e.%202%3A16%20is%20manually%20changed%20to%20%3CSTRONG%3E00%3A02%3A16%3C%2FSTRONG%3E%20--%26gt%3B%20the%20I%20am%20able%20to%20convert%20it%20to%20seconds%20(using%20%5Bss%5D)%20but%20it%20is%20impossible%20to%20%3CSTRONG%3Emanually%20add%20%2200%3A%22%3C%2FSTRONG%3E%20before%20each%20number.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAny%20kind%20suggestions%20from%20you%20would%20be%20%3CSTRONG%3Ereally%2C%20really%20appreciated%3C%2FSTRONG%3E!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EYour%20brother%20in%20distress%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-201738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201780%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201780%22%20slang%3D%22en-US%22%3E%3CP%3EJayant%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201776%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201776%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152529%22%20target%3D%22_blank%22%3E%40Arul%20Tresoldi%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3Eexcel%20assumes%202%3A16%20is%20automatically%20hour%3Aminutes%3B%20as%20you%20said%2C%20if%20you%20manually%20insert%20%2200%3A%22%20it%20understands%20that%202%20is%20minutes%20and%20not%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20adding%20%2200%3A%22%20manually%20in%20each%20cell%20should%20be%20worth%20it%2C%20so%20I'm%20asking%20IF%20you%20can%20add%20a%20column%20to%20the%20file%2C%20to%20do%20your%20job.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%2C%20then%20it's%20easy!%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3DTEXT(%2200%3A%22%26amp%3BTEXT(HOUR(A2)%3B%2200%22)%26amp%3B%22%3A%22%26amp%3BTEXT(MINUTE(A2)%3B%2200%22)%3B%22%5Bss%5D%22)%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EIt%20just%20convert%20a%20new%20string%20of%20values%20made%20by%20adding%20%2200%3A%22%20to%20the%20given%20datas%20(intended%20as%20hour%20and%20minutes)%20to%20convert%20it%20again%20in%20%5Bseconds%5D%20after%20it%20understands%20that%20there%20are%203%20slots%20of%20numbers%2C%20so%20it%20must%20be%20hours%3Aminutes%3Aseconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attachement!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eedit%3A%20or%20just%20like%20mr.Baklan%20said%2C%20just%20multiply%20the%20given%20data%20*24*60%20and%20there%20will%20be%20the%20seconds.%20Then%20you'll%20make%20all%20ops%20you%20want%20with%20that%20data.%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152529%22%20target%3D%22_blank%22%3E%40Arul%20Tresoldi%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20kind%20help!%20As%20I%20wrote%20to%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20I%20wasn't%20even%20sure%20if%20anyone%20would%20even%20reply%20to%20my%20message!%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20really%20kind%20-%20just%20when%20I%20wasn't%20sure%20how%20to%20use%20the%20formula%20you%20shared%2C%20I%20see%20a%20lovely%20attachment%2C%20explaining%20how%20to%20use%20it.%20OMG!%20You're%20so%20cool!!%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20taking%20out%20time%20and%20replying%20to%20me%20-%20your%20solution%20worked%20beautifully!%3C%2FP%3E%3CP%3EI%20am%20smiling%20ear%20to%20ear%20as%20I%20thank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EGod%20bless!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201773%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201773%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%20Jayant%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you'd%20only%20like%20to%20calculate%20the%20average%20you%20may%20use%3C%2FP%3E%3CPRE%3E%3DAVERAGE(%26lt%3Byou%20cells%26gt%3B)*24*60%3C%2FPRE%3E%3CP%3Ewhich%20will%20return%20the%20average%20in%20seconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EI%20am%20so%20thankful%20to%20you.%20Honestly%2C%20I%20wasn't%20even%20sure%20anyone%20would%20even%20reply%20to%20my%20message!%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20taking%20out%20time%20and%20replying%20to%20me%20-%20your%20solution%20worked%20beautifully!%3C%2FP%3E%3CP%3EI%20am%20smiling%20ear%20to%20ear%20as%20I%20thank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EGod%20bless!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201753%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201753%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20also%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20416px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35566iAF462BC5FCEC1116%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201749%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201749%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3Eexcel%20assumes%202%3A16%20is%20automatically%20hour%3Aminutes%3B%20as%20you%20said%2C%20if%20you%20manually%20insert%20%2200%3A%22%20it%20understands%20that%202%20is%20minutes%20and%20not%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20adding%20%2200%3A%22%20manually%20in%20each%20cell%20should%20be%20worth%20it%2C%20so%20I'm%20asking%20IF%20you%20can%20add%20a%20column%20to%20the%20file%2C%20to%20do%20your%20job.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%2C%20then%20it's%20easy!%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3DTEXT(%2200%3A%22%26amp%3BTEXT(HOUR(A2)%3B%2200%22)%26amp%3B%22%3A%22%26amp%3BTEXT(MINUTE(A2)%3B%2200%22)%3B%22%5Bss%5D%22)%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EIt%20just%20convert%20a%20new%20string%20of%20values%20made%20by%20adding%20%2200%3A%22%20to%20the%20given%20datas%20(intended%20as%20hour%20and%20minutes)%20to%20convert%20it%20again%20in%20%5Bseconds%5D%20after%20it%20understands%20that%20there%20are%203%20slots%20of%20numbers%2C%20so%20it%20must%20be%20hours%3Aminutes%3Aseconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attachement!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eedit%3A%20or%20just%20like%20mr.Baklan%20said%2C%20just%20multiply%20the%20given%20data%20*24*60%20and%20there%20will%20be%20the%20seconds.%20Then%20you'll%20make%20all%20ops%20you%20want%20with%20that%20data.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-201748%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201748%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jayant%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you'd%20only%20like%20to%20calculate%20the%20average%20you%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DAVERAGE(%26lt%3Byou%20cells%26gt%3B)*24*60%3C%2FPRE%3E%0A%3CP%3Ewhich%20will%20return%20the%20average%20in%20seconds.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1820843%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1820843%22%20slang%3D%22en-US%22%3EI%20want%20to%20go%20the%20other%20way%2C%20example%20given%3A%20I%20have%20number%20of%20seconds%20in%20my%20case%2C%20the%20average%20number%20of%20seconds%20to%20cover%205oo%20meters%20rowing%3A%20i.e.%20160%20seconds%2C%20need%20to%20be%20converted%20into%20a%20format%3A%20mm%3Ass%2C%20leading%20to%2002%3A40%20164%20seconds%20leading%20to%2002%3A44%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824868%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20number%20of%20a%20format%20(2%3A16)%20into%20minutes%20and%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F846723%22%20target%3D%22_blank%22%3E%40jjrtoussaint-nl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20actually%20days%20are%20integers%20and%20time%20is%20decimal%20part%20of%20the%20number.%20In%20calculations%20one%20day%20is%20equal%20to%201%2C%20thus%20one%20hour%20%3D%201%2F24%3B%20one%20minute%20%3D%201%2F24%2F60%20and%20one%20second%20%3D%201%2F24%2F60%2F60.%20With%20that%20you%20may%20do%20conversions%20from%20numbers%20to%20time%20and%20back%2C%20just%20apply%20proper%20format%20where%20necessary.%20Your%20sample%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20269px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229630i541083DFBDCBDB09%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

My Dear Friends,

 

  • I have been struggling for almost half a day (!) now to convert some number that my team sends me, in excel, which are in the format like 2:16 or 1:07 etc.
  • These are the minutes & seconds recorded by them for a certain activity. The excel (sent by team) shows these number / cells formatted as 'General' and no special formatting is applied by the team.
  • I now intent to convert this into seconds to find the average etc.
  • I tried several options like 'Custom formatting' to '[ss]' etc. but none seems to be working.
  • The only way it worked till now is when I add "00:" manually before each number. i.e. 2:16 is manually changed to 00:02:16 --> the I am able to convert it to seconds (using [ss]) but it is impossible to manually add "00:" before each number.

Any kind suggestions from you would be really, really appreciated!

 

Regards,

Your brother in distress :(

 

10 Replies
Highlighted

Hi Jayant,

 

If you'd only like to calculate the average you may use

=AVERAGE(<you cells>)*24*60

which will return the average in seconds.

 

Highlighted

Hi there,

excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.

 

I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.

 

If you can, then it's easy!

=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")

It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.

 

See attachement!

 

edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data. 

Highlighted
Highlighted

@Sergei Baklan wrote:

Hi Jayant,

 

If you'd only like to calculate the average you may use

=AVERAGE(<you cells>)*24*60

which will return the average in seconds.

 


Dear @Sergei Baklan,

I am so thankful to you. Honestly, I wasn't even sure anyone would even reply to my message! 

Thanks so much for taking out time and replying to me - your solution worked beautifully!

I am smiling ear to ear as I thank you :)

God bless!

Highlighted

@Arul Tresoldi wrote:

Hi there,

excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.

 

I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.

 

If you can, then it's easy!

=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")

It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.

 

See attachement!

 

edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data. 


Dear @Arul Tresoldi,

Thank you so much for your kind help! As I wrote to @Sergei Baklan, I wasn't even sure if anyone would even reply to my message! 

You are really kind - just when I wasn't sure how to use the formula you shared, I see a lovely attachment, explaining how to use it. OMG! You're so cool!!

Thanks so much for taking out time and replying to me - your solution worked beautifully!

I am smiling ear to ear as I thank you :)

God bless!

Highlighted

Jayant, you are welcome

Highlighted
I want to go the other way, example given: I have number of seconds in my case, the average number of seconds to cover 5oo meters rowing: i.e. 160 seconds, need to be converted into a format: mm:ss, leading to 02:40 164 seconds leading to 02:44
Highlighted

@jjrtoussaint-nl 

In Excel actually days are integers and time is decimal part of the number. In calculations one day is equal to 1, thus one hour = 1/24; one minute = 1/24/60 and one second = 1/24/60/60. With that you may do conversions from numbers to time and back, just apply proper format where necessary. Your sample:

image.png

Highlighted

@Sergei Baklan one wishes life will always be so easy, super, it is not just the formula that helped, but also the way approaching the problem, Thanks very much! 

Highlighted