Excel Q: Displaying Integer as Minutes and Seconds.

Factual Questions
1

How do I get Excel to display the number 11.2 in the format mm:ss, i.e.: 11:12?

2

=TEXT(INT(A1),“00”) &":"& TEXT(((A1-INT(A1))*60),“00”)

Works, but there’s probably a more elegant way (for example an easier way to get just the decimal portion)

3

BTW, 11.2 isn’t an integer.

4

[QUOTE=Mangetout]
BTW, 11.2 isn’t an integer.
[/QUOTE]

It is in base 0.2. :stuck_out_tongue:

5

=A1/24, format the cell as a Time.

[ETA] oops, that’s hours, not minutes. Should be:

=A1/1440, format the cell as mm:ss

Course, that wraps around at 60 minutes.

6

[QUOTE=Usram]
=A1/1440, format the cell as mm:ss
[/QUOTE]

I get the message that the formula contains an error.

7

Sure you typed it in right? Does the error message say what the problem is?

8

Try this:



=FLOOR(B4,1) & ":" & ROUND(((B4 - INT(B4)) * 60),  0)

The last number controls the number of decimal places you want to see in your seconds; it will round to the nearest second as it is.

9

[QUOTE=Usram]
Sure you typed it in right? Does the error message say what the problem is?
[/QUOTE]

I copied and pasted the formula exactly as you wrote it (with my data in cell A1). The error message didn’t provide any more details.

[QUOTE= Dervorin]
=FLOOR(B4,1) & “:” & ROUND(((B4 - INT(B4)) * 60), 0)
[/QUOTE]

That works, but, Oy! I’d’ve thought Excel had an easier way.

10

[QUOTE=Nobody Special]
I copied and pasted the formula exactly as you wrote it (with my data in cell A1).
[/QUOTE]

Wait… you didn’t include the “format as mm:ss” part, did you? That was not part of the formula… I meant, “then format the cell as mm:ss”.

11

Just a minor nitpick - Dervorin’s method doesn’t render the time with leading zeroes where needed - so 11.1 turns into 11:6 rather than 11:06

Mine does. Nyah!

12

[QUOTE=Usram]
Wait… you didn’t include the “format as mm:ss” part, did you? That was not part of the formula… I meant, “then format the cell as mm:ss”.
[/QUOTE]

Um…No…Of course not…I’d have to be an idiot to do that! :smack:

13

Well, I thought it was a long shot :smiley:

14

[QUOTE=Mangetout]
Just a minor nitpick - Dervorin’s method doesn’t render the time with leading zeroes where needed - so 11.1 turns into 11:6 rather than 11:06

Mine does. Nyah!
[/QUOTE]

I did notice that, and I accept your nyah. I’d like to think mine’s more elegant mathematically, even if it doesn’t provide the required result. That’s all Excel’s fault! :stuck_out_tongue: