SOLVED

Format a number like 34.8 as 34 min 48 sec

Brass 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
best response confirmed by perkin_warbeck (Brass 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 

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

@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

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

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

@perkin_warbeck 

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

image.png

 

1 best response

Accepted Solutions
best response confirmed by perkin_warbeck (Brass 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 

View solution in original post