Apache OpenOffice Community Forum

Win 7

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

If your minutes are in cell A1

Minutes Seconds hundredths 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).

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?

TomBrooklyn wrote: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")

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.

TomBrooklyn wrote: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
Okay, we see we have 15 seconds, and it's just the integer portion of the number (15), which is
To get seconds and hundredths then, we need to work with the fractional portion (.7565), which we can get from
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
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
This gives 39/100 = .39 seconds, which we just multiply by 100 to get the remaining hundredths.
Now, the formula, we used to get the fraction
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
Our whole number of seconds is still INT(A1), but the seconds formula simplifies (and becomes more efficient), by
and our hundredths becomes
And I see that Acknak attached an example while I was typing this. Cool!