Select the entire column (column H in my example) by selecting the column header. 
Copy (Ctrl+C) the result in the first cell with the formula (H4 in my example).To apply this formula to the entire column: When you release the mouse, all those cells you dragged this formula over will be converted to minutes and seconds.(See below for how to apply it to ALL cells in the column.) 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.And the G4 was the reference cell for my H4 cell.) 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. In the formula bar, type =G4/86400 then press Enter.
Go to the first cell in the new column that pairs with a cell in the seconds column. 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. 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. 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. Column G in the screen shot below has a long list of seconds. 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. But if you have hundreds or thousands of these conversions, you need a formula for doing that. If you only have a couple to do, no big deal - just figure it out in your head. Likewise, 247 seconds divided by 60 is 4.11, whereas 247 seconds is actually 4 minutes and 7 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). You can’t just divide by 60 - the result of that will be minutes and decimal fractions of a minute, not minutes and seconds. You’d think that converting seconds to minutes in an Excel spreadsheet would be simple, but it’s not.
0 kommentar(er)
