Forum Discussion

Brass Contributor

Jul 25, 2019

Solved

Format a number like 34.8 as 34 min 48 sec

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

excel

office 365

Man Fai Chan
Jul 26, 2019
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

Iron Contributor

Jul 26, 2019

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

crfraser
Copper Contributor
Jul 26, 2019
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
Brass Contributor
Jul 26, 2019
Man Fai Chan Thank you. That's a great answer!