Forum Discussion
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 hours, just minutes. For example, the value 80.69644725 should display as
80 m 42s
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.
6 Replies
One more variant for the collection - divide number on 1440 and apply to the result custom format as
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
Hope that helps
Nabil Mourad
nabilmourad Another very good answer! Thank you for your help.
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.
You could change the approach slightly and get it all in one cell:
=INT(A1)&" Min "&ROUND((A1-(INT(A1)))*60,0)&" Sec"
Man Fai Chan Thank you. That's a great answer!
