h1

Excel: Convert seconds to minutes

April 27, 2012

You’d think that converting seconds to minutes in an Excel spreadsheet would be simple, but it’s not. You can’t just divide by 60 — the result of that will be minutes and decimal fractions of a minute, not minutes and seconds. For example, if you divide 78 seconds by 60, you get 1.3, but 1.3 is NOT the same as 1:18 (1 minute and 18 seconds). Likewise, 247 seconds divided by 60 is 4.11, whereas 247 seconds is actually 4 minutes and 7 seconds. If you only have a couple to do, no big deal — just figure it out in your head. But if you have hundreds or thousands of these conversions, you need a formula for doing that.

To convert seconds to minutes, you have to divide the seconds by the total number of seconds in a day, then tell Excel to display the result in mm:ss format. Simple, huh? ;-)

Here’s what I did…

  1. Column G in the screen shot below has a long list of seconds. Because I need to reference column G and put the results into a new column, I inserted a new column (H) and called it mm:ss.
  2. However, inserting a new column isn’t enough — I have to format the cells in that column so that they display the result in minutes and seconds. To format the column, select the column header, right-click on it, select Format Cells, select Custom on the Number tab, then select mm:ss from the list of types. Click OK.
  3. Go to the first cell in the new column that pairs with a cell in the seconds column. In my example, that was H4.
  4. In the formula bar, type =G4/86400 then press Enter. The seconds convert into minutes and seconds! (But where does that 86400 come from? That’s the number of seconds in a day — 60 x 60 x 24 = 86400. And the G4 was the reference cell for my H4 cell.)
  5. Now, click in the H4 cell and ‘grab’ the bottom right handle of the cell marker and drag it down the other cells in column H. (See below for how to apply it to ALL cells in the column.)

  6. When you release the mouse, all those cells you dragged this formula over will be converted to minutes and seconds.

To apply this formula to the entire column:

  1. Copy (Ctrl+C) the result in the first cell with the formula (H4 in my example).
  2. Select the entire column (column H in my example) by selecting the column header.
  3. Paste (Ctrl+V).
  4. You’ll have to rename the column back to mm:ss, but you’ll have that formula now applied to every row of your spreadsheet.

These sites helped me figure out what to do:

[Links last checked April 2012]

13 comments

  1. It worked, thanks for the advice! I will use this formula from now on.


  2. Yes it worked perfect, great advice. Thanks.


  3. Formula worked great, very easy!


  4. Good job – simple and easy


  5. Also works the other way around. Input 00:00 multiply by (60*60*24) and it gives you the seconds.


  6. THANKS IT WORKS IT IS HELP FULL


  7. Awesome. Thanks. After searching through dozens of websites (which were useless as they were converting seconds into minutes + decimal fractions of a minute), I finally found the right solution here and it was very simple and straightforward.


  8. I need it to go to thousands of a second. so it would be mm:ss.xx,


  9. sorry hundreds not thousands of a second.


  10. Thanks:)


  11. Great. Thank you.


  12. Very nice conversion format. How do I remove the 0 in the times shown in min:sec. Ex. 63.5 sec. is shown as 01:03.5. We don’t show the 0 in printing the final times of horses that ran in a particular race. Your help will be fully appreciated…GOS BLESS !!!


  13. Genious! Thank you, exactly what I needed.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 332 other followers

%d bloggers like this: