Minutes/seconds in format like mmm.ss

[jennfurr]

I've got some times that I need to input into a spreadsheet, like 107m 31s and 202m 54s.

I need to have them display as something like in mm.ss (like 107.31 and 202.54). I do not want the minutes to convert to hours, and I need to be able to do calculations off of the entries.

for example, say I want to divide 107.31 by seven to determine how many minutes/seconds were done per day on average.

Possible?

 

[iggy_]

Here is one solution if you enter the values in the format you described and want the result in the same format.

=TRUNC(L3/4)+(TRUNC(MOD(L3,4))*60+(L3-TRUNC(L3))*100)/4/100

Where 4 is just the number of days I'm using to divide by.

 

[joeu2004]

@jennfurr.... I'm not sure what problem you want to solve.

If you want to enter 107m 31s as Excel time, type 107:31.0 or 0:107:31 .

You can use those forms to enter up to 9999 minutes. Can you live with that limitation?

In the first form, the redundant ".0" is necessary for Excel to distinguish between h:m and m:s data entry.

In the second form, the leading "0:" avoids the ambiguity by providing all three fields h:m:s.

-----

If you want to type 107m 31s in A1 and convert it to Excel time, you need to use a formula in like the following in another cell:

=--SUBSTITUTE(SUBSTITUTE(A1,"m ",":"),"s",".0")

In all cases, format the cell as Custom [m].ss .

-----

If you want the division to be rounded or truncated to the second, choose one of the following, assume A1 contains the time:

Round:
=--TEXT(B1/7, "[m]:s.0")

Truncate:
=--TEXT(INT(B1/7 * 86400) / 86400, "[m]:s.0")

The double negate converts text to numeric Excel time.

--TEXT(...,"[m]:s.0") is needed for truncation only if you want to ensure that internal binary representation is exact for the decimal representation. For example, if you might compare the calculated Excel time.