Very likely the dumbest Excel question ever asked

April 3, 2006 10:28 AM

MS Excel: How do I convert a number representing seconds into minutes:seconds?

This ought to be simple enough, but I can't hit upon anything that works. I've checked the help index in Excel, looked online on Microsoft's help pages, and through AskMetafilter archives tagged 'Excel' and so far, nothing solves this problem.*

I have a large amount of data that represent durations of time in seconds. (e.g., 512, 726, 322....). I need to take all these values and convert them to mm:ss format (6:32, 12:06, 5:22...). I only need minutes and seconds. None of the numbers is large enough to need an hour component.

MS Excel help says to use the formula to convert times:

=TEXT(A1/24, "h:mm") where A1 is my data cell and h:mm is the output

I tried adjusting this to =TEXT(A1/60, "mm:ss"). This does not work. The results I get are completely innacurate. For example, I tried to convert 215 seconds with this formula, and my result was 7:58. Boggling. 215 seconds by my calculation is 3:35.

I'm on the verge of hacking out a brute-force method to do this (divide # by 60, discard remainder and multiply result by 60 again, subtract this product from my original # to get #seconds, and display as whole#result:#seconds), but before I do that I thought somebody here may have a better solution.

*(It actually seems like the discussion groups for Excel on the Microsoft Office website may actually cover and answer my question; unfortunately, here at work, ActiveX is disabled on all browsers by our IT department (non-reenableable by me), and I can't read the *@#^!@$ threads without it. Discussions about the inherrent stupidity of this situation will likely be entertaining, but mostly will rub salt into some slowly healing wounds. Assume for the sake of the question that IT is standing firm on this ridiculous policy.)

posted by contessa to computers & internet (13 comments total)

=CONCATENATE(FLOOR(A1/60,1),":",IF(MOD(A1,60)<1 0,concatenate(0,mod(a1,60)),mod(a1,60)))br>
Works for me. I hope this doesn't get mangled. And no, it's not very elegant.
posted by jwadhams at 10:35 AM on April 3, 2006

I don't have an answer for you, but I can assure you that this is nowhere near the dumbest Excel question ever asked.

So you've got that going for ya'. :)
posted by unixrat at 10:35 AM on April 3, 2006 [1 favorite has favorites]

OK, it did get mangled. Copy the whole thing without the br> and accept the auto correction (or at least Excel 2003 fixed it for me, it's probably short or long a paren)
posted by jwadhams at 10:37 AM on April 3, 2006

Crazy, but here it is:

In a new cell divide the seconds cell by (24*60*60) then format with Format...Cell...and whatever hours:minutes format you want.

I got it by searching google for "convert seconds to minutes and seconds in Excel."

It's on this page.
posted by sevenless at 10:38 AM on April 3, 2006

The problem you are experiencing is A1/24 results in hours. Likewise, A1/24/60 results in hours, and A1/24/60/60 results in seconds. Therefore, you want the following function:

=TEXT(A1/24/60/60, "mm:ss")

posted by sequential at 10:40 AM on April 3, 2006

Preview be damned. Yes, you can also do this:

=TEXT(A5/(24*60*60), "mm:ss")

posted by sequential at 10:41 AM on April 3, 2006

This works for me: =TEXT(A1/86400,"[m]:ss") and use [mm] if you want a leading zero.

If you look up the help topic "Number format codes", you see that you need to use [] around one of the formats to make it be elapsed time. I figured out dividing the number of seconds by 86400 since =TEXT(1,"[ss]") gives 86400 as the output.
posted by skynxnex at 10:42 AM on April 3, 2006

And of course, I just tried without the [] and it worked as well, so nevermind me.
posted by skynxnex at 10:46 AM on April 3, 2006

Wow. Thanks!!! Sequential's is the one I have decided to use, but jwadhams gets a huge kiss on the cheek for hacking it out brute-force style.

Again, huge thanks, you've made my life immesurably easier today.
posted by contessa at 10:48 AM on April 3, 2006

Just fyi jwadhams, the munge is the addition of a space in the number "10" (when checking the total seconds).
posted by inigo2 at 10:53 AM on April 3, 2006

A1/24/60 results in hours¹

By which, of course, I mean A1/24/60 results in minutes.

For values of greater than 3600, an hour is added and the minutes reset to zero. If you're only going for something formated in MM:SS, you need to use another formula. Note that this formula leaves you with some formatting issues.

=CONCATENATE(INT(A1/60),":",MOD(A1,60))

What is it you'd like to happen when the number of minutes exceeds 60, if that's even possible?
posted by sequential at 10:59 AM on April 3, 2006

sequential: What is it you'd like to happen when the number of minutes exceeds 60, if that's even possible?

With the dataset that I have to work with, and for this particular example, the number of minutes is never going to exceed 60. If I were to find a result where it did, and I needed hh:mm:ss, I guess I could modify what I'm using to:

=TEXT(A1/24/60/60, "hh:mm:ss")

Which would work. hopefully.

Very grateful for all the help & answers today!!
posted by contessa at 11:33 AM on April 3, 2006

Contessa, that does work. :-)
posted by sequential at 11:36 AM on April 3, 2006