Jul 25 2019 04:14 PM
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
Jul 25 2019 05:26 PM
SolutionYou 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.
Jul 25 2019 06:43 PM
@Man Fai Chan Thank you. That's a great answer!
Jul 25 2019 06:47 PM - edited Jul 25 2019 06:49 PM
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
Jul 25 2019 07:00 PM
@nabilmourad Another very good answer! Thank you for your help.
Jul 26 2019 01:33 AM
You could change the approach slightly and get it all in one cell:
=INT(A1)&" Min "&ROUND((A1-(INT(A1)))*60,0)&" Sec"
Jul 26 2019 02:04 AM
One more variant for the collection - divide number on 1440 and apply to the result custom format as
Jul 25 2019 05:26 PM
SolutionYou 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.