SOLVED

Format a number like 34.8 as 34 min 48 sec

%3CLINGO-SUB%20id%3D%22lingo-sub-774330%22%20slang%3D%22en-US%22%3EFormat%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774330%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20numeric%20valuess%20representing%20the%20number%20of%20minutes%2C%20for%20example%2056.69644725.%26nbsp%3B%20I%20want%20to%20display%20it%20as%20a%20number%20of%20whole%20minutes%20and%20seconds%2C%20e.g.%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E34%20m%2042%20s%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20want%20to%20display%20hours%2C%20just%20minutes.%26nbsp%3B%20For%20example%2C%20the%20value%2080.69644725%20should%20display%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E80%20m%2042s%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-774330%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774353%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774353%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20have%20to%20break%20down%20the%20time%20by%20yourself.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EFor%20example%2C%20A1%20%3D%2080.1234%3CBR%20%2F%3EThe%20minute%20part%20in%20B1%20%3D%20%22%3DINT(A1)%22%20to%20get%20the%20number%2080%20as%20minute.%3C%2FP%3E%3CP%3EThe%20second%20part%20in%20C1%20%3D%20%22%3D(A1-INT(A1))*60%22%20to%20get%20the%20second%20(7.4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20D1%2C%20you%20may%26nbsp%3B%20concatenate%20the%20string%20as%20%22%3D%20B1%20%26amp%3B%20%22%20min%20%22%20%26amp%3B%20C1%20%26amp%3B%20%22%20sec%22%20%22%3C%2FP%3E%3CP%3EYou%20may%20also%20consider%20the%20function%20TIME%20and%20write%20the%20formula%20in%20D1%20as%20%3D%20TIME(0%2CB1%2CC1)%20and%20then%20custom%20the%20cell%20format%20as%20%22%20m%20%22min%22%20s%20%22sec%22%20%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20this%20help%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774386%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53148%22%20target%3D%22_blank%22%3E%40Man%20Fai%20Chan%3C%2FA%3E%26nbsp%3B%20Thank%20you.%26nbsp%3B%20%26nbsp%3BThat's%20a%20great%20answer!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774392%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EYou%20can%20use%20this%20function%3A%3C%2FP%3E%3CP%3E%3DINT(MOD(A1%2C60))%26amp%3B%22%20Min%20%22%26amp%3BINT((MOD(A1%2C60)-INT(MOD(A1%2C60)))*60)%26amp%3B%22%20Sec%22%3C%2FP%3E%3CP%3EHere%20is%20a%20screenshot%3C%2FP%3E%3CP%3Eand%20I%20attached%20a%20sample%20Excel%20File%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MinSec%20Sample.png%22%20style%3D%22width%3A%200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124555iBAA5A36349371BDA%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20width%3D%220%22%20height%3D%220%22%20title%3D%22MinSec%20Sample.png%22%20alt%3D%22MinSec%20Sample.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MinSec%20Sample.png%22%20style%3D%22width%3A%20580px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124556iB65D870006511214%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MinSec%20Sample.png%22%20alt%3D%22MinSec%20Sample.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774426%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3BAnother%20very%20good%20answer!%26nbsp%3B%20Thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774710%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20a%20number%20like%2034.8%20as%2034%20min%2048%20sec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774710%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53148%22%20target%3D%22_blank%22%3E%40Man%20Fai%20Chan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382386%22%20target%3D%22_blank%22%3E%40perkin_warbeck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20change%20the%20approach%20slightly%20and%20get%20it%20all%20in%20one%20cell%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINT(A1)%26amp%3B%22%20Min%20%22%26amp%3BROUND((A1-(INT(A1)))*60%2C0)%26amp%3B%22%20Sec%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have numeric valuess representing the number of minutes, for example 56.69644725.  I want to display it as a number of whole minutes and seconds, e.g.,

 

34 m 42 s

 

I do not want to display hours, just minutes.  For example, the value 80.69644725 should display as

 

80 m 42s

 

6 Replies
Highlighted
Best Response confirmed by perkin_warbeck (Contributor)
Solution

You may have to break down the time by yourself. 

For example, A1 = 80.1234
The minute part in B1 = "=INT(A1)" to get the number 80 as minute.

The second part in C1 = "=(A1-INT(A1))*60" to get the second (7.4)

 

In D1, you may  concatenate the string as "= B1 & " min " & C1 & " sec" "

You may also consider the function TIME and write the formula in D1 as = TIME(0,B1,C1) and then custom the cell format as " m "min" s "sec" ".

 

Hope that this help you. 

 

@perkin_warbeck 

Highlighted

@Man Fai Chan  Thank you.   That's a great answer!

Highlighted

@perkin_warbeck 

Hi

You can use this function:

=INT(MOD(A1,60))&" Min "&INT((MOD(A1,60)-INT(MOD(A1,60)))*60)&" Sec"

Here is a screenshot

and I attached a sample Excel File

MinSec Sample.pngMinSec Sample.png

 

Hope that helps

Nabil Mourad

Highlighted

@nabilmourad Another very good answer!  Thank you for your help.

Highlighted

@Man Fai Chan @perkin_warbeck 

 

You could change the approach slightly and get it all in one cell:

 

=INT(A1)&" Min "&ROUND((A1-(INT(A1)))*60,0)&" Sec"

Highlighted

@perkin_warbeck 

One more variant for the collection - divide number on 1440 and apply to the result custom format as

image.png