How Convert Decimal Minutes to Minutes and Seconds?

[TomBrooklyn]

Win 7

How can I convert minutes given in a whole and decimal number to minutes, seconds, and hundredths of a second?

[Charlie Young]

If your minutes are in cell A1

Minutes

Code: Select all

=INT(A1)

Seconds

Code: Select all

=INT(60*(A1-INT(A1)))

hundredths

Code: Select all

=100*(60*(A1-INT(A1))-INT(60*(A1-INT(A1))))

Note that if you divide the number by 1440 = 24*60, you can format the result as MM:SS.00 to display all the stuff in one cell (this works for minutes < 60).

[TomBrooklyn]

I'm sorry. I don't understand your reply. What am I supposed to do with the code you show? Where am I supposed to put it?

[keme]

I'm sorry. I don't understand your reply. What am I supposed to do with the code you show? Where am I supposed to put it?

If necessary, edit the formulas to get the correct cell address for your decimal minutes (change every occurrence of "A1" if the number is in a different cell)
Then put the required formula in whichever cell you need the minutes, seconds, and/or hudredths.

Note that due to limitations in numerical representation, rounding errors may occur with some values. If your calculations have critical impact, always be aware of the limitations of your calculation tools.

If you need the conversion for display purposes only, conversion to number base "days" (by division as Charlie Young explained) and then formatting (directly in the cell or by using the TEXT() function) is better. As far as I can see, that approach won't be vulnerable to rounding errors.
Subsequent calculation should always be based on your original data, not on data thus converted for display purposes.

Also, if you want to display accumulated minutes >=60, put the MM section of the format string in square brackets.

=TEXT(A1/1440;"[MM]:SS.00")

[acknak]

Here's a sample you can study to see how the formulas fit together. Download the attached document and open in Calc. Enter a time and see how the breakout values you want are calculated.

[Charlie Young]

I'm sorry. I don't understand your reply. What am I supposed to do with the code you show? Where am I supposed to put it?

I think Keme has already explained this (and thanks for the [MM] trick, I had forgotten that!), but the formulas themselves maybe could use some further discussion, and improvement.

Let's use a simple example. Suppose A1 contains

Code: Select all

15.7565

Okay, we see we have 15 seconds, and it's just the integer portion of the number (15), which is

Code: Select all

=INT(A1)

To get seconds and hundredths then, we need to work with the fractional portion (.7565), which we can get from

Code: Select all

=A1-INT(A1)

though there is a better way, discussed below.

.75 is ¾ of a minute of course (i.e., 45 = 60 * .75 seconds), so for the whole number of seconds

Code: Select all

=INT(60*(A1-INT(A1)))

which leaves 65/10000 = .0065 seconds.

So we take our original fraction portion (.7565), multiply it by 60 to convert it to seconds and fractions of a second, then subtract off the seconds we got above

Code: Select all

=60*(A1-INT(A1))-INT(60*(A1-INT(A1)))

This gives 39/100 = .39 seconds, which we just multiply by 100 to get the remaining hundredths.

Code: Select all

=100*(60*(A1-INT(A1))-INT(60*(A1-INT(A1))))

Now, the formula, we used to get the fraction

Code: Select all

A1-INT(A1)

can be improved. Computers are very good at splitting numbers into quotients and remainders. The remainder is called the MOD function, and our .7565 is the remainder of dividing 15.7565 by 1

Code: Select all

=MOD(A1;1)

Our whole number of seconds is still INT(A1), but the seconds formula simplifies (and becomes more efficient), by

Code: Select all

=INT(60*MOD(A1;1))

and our hundredths becomes

Code: Select all

=100*(60*MOD(A1;1)-INT(60*MOD(A1;1)))

And I see that Acknak attached an example while I was typing this. Cool!