Excel: Convert seconds to minutes

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:

• http://www.mrexcel.com/archive/Dates/307.html
• http://www.pcreview.co.uk/forums/convert-seconds-minutes-and-seconds-excel-t1772961.html
• http://answers.yahoo.com/question/index?qid=20080804112727AA9LUBh

[Links last checked April 2012]