# Excel Q: Displaying Integer as Minutes and Seconds. **URL:** \ **Category:** Factual Questions\ **Created:** [June 6, 2008, 10:45am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002 "2008-06-06T10:45:04Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Nobody\_Special](https://avatars.discourse-cdn.com/v4/letter/n/bbce88/32.png) [@Nobody\_Special](https://boards.straightdope.com/u/Nobody_Special)\ **Post date:** [June 6, 2008, 10:45am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/1 "2008-06-06T10:45:04Z")
How do I get Excel to display the number 11.2 in the format mm:ss, i.e.: 11:12? ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [June 6, 2008, 10:54am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/2 "2008-06-06T10:54:10Z")
=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) ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [June 6, 2008, 10:56am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/3 "2008-06-06T10:56:04Z")
BTW, 11.2 isn’t an integer. ---
**Author:** ![Colophon](https://avatars.discourse-cdn.com/v4/letter/c/f05b48/32.png) [@Colophon](https://boards.straightdope.com/u/Colophon)\ **Post date:** [June 6, 2008, 11:07am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/4 "2008-06-06T11:07:38Z")
[QUOTE=Mangetout] BTW, 11.2 isn’t an integer. [/QUOTE] It is in base 0.2. 😛 ---
**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\ **Post date:** [June 6, 2008, 11:28am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/5 "2008-06-06T11:28:28Z")
=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. ---
**Author:** ![Nobody\_Special](https://avatars.discourse-cdn.com/v4/letter/n/bbce88/32.png) [@Nobody\_Special](https://boards.straightdope.com/u/Nobody_Special)\ **Post date:** [June 6, 2008, 11:39am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/6 "2008-06-06T11:39:53Z")
[QUOTE=Usram] =A1/1440, format the cell as mm:ss [/QUOTE] I get the message that the formula contains an error. ---
**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\ **Post date:** [June 6, 2008, 11:56am UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/7 "2008-06-06T11:56:46Z")
Sure you typed it in right? Does the error message say what the problem is? ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [June 6, 2008, 12:15pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/8 "2008-06-06T12:15:16Z")
Try this: ```auto =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. ---
**Author:** ![Nobody\_Special](https://avatars.discourse-cdn.com/v4/letter/n/bbce88/32.png) [@Nobody\_Special](https://boards.straightdope.com/u/Nobody_Special)\ **Post date:** [June 6, 2008, 1:06pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/9 "2008-06-06T13:06:09Z")
[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. ---
**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\ **Post date:** [June 6, 2008, 1:16pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/10 "2008-06-06T13:16:02Z")
[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”. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [June 6, 2008, 1:18pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/11 "2008-06-06T13:18:07Z")
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! ---
**Author:** ![Nobody\_Special](https://avatars.discourse-cdn.com/v4/letter/n/bbce88/32.png) [@Nobody\_Special](https://boards.straightdope.com/u/Nobody_Special)\ **Post date:** [June 6, 2008, 1:43pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/12 "2008-06-06T13:43:35Z")
[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: ---
**Author:** ![Ximenean](https://avatars.discourse-cdn.com/v4/letter/x/aca169/32.png) [@Ximenean](https://boards.straightdope.com/u/Ximenean)\ **Post date:** [June 6, 2008, 1:49pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/13 "2008-06-06T13:49:26Z")
Well, I thought it was a long shot 😃 ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [June 6, 2008, 2:10pm UTC](https://boards.straightdope.com/t/excel-q-displaying-integer-as-minutes-and-seconds/452002/14 "2008-06-06T14:10:09Z")
[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! 😛