Convert Seconds with the TIME Function in Excel

I often download an iTunes playlist just to see the time increments for each song, but iTunes only gives you the number of seconds for each song duration. In a previous post I spent a great deal of time showing how to use a combination of various formulas to convert these seconds, when it’s really very simple.

Excel Formula to Convert Seconds to Minutes

You see, the Excel formula to convert seconds to minutes can be used easily with the TIME Function. All you have to do is format a worksheet cell with the proper time format.

Excel Formula to Convert Seconds to Minutes

The formula for Min:Sec in cell F2 is shown in this Function Arguments dialog box.

Function Arguments Dialog Box Seconds Conversion

The TIME Function has three arguments, but in this formula the first two (Hour, Minute) are not needed. Excel takes the last argument for Second and converts to a time serial number. You can see the formula result is 03:57 because cell F2 is using a custom time format.

Format Cells Custom Time Format

To format a range with a custom time format that shows minutes and seconds, use the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Number tab, and under Category select Custom, then enter mm:ss in the Type text box and click OK.

Bonus Formula and Formatting

The Cum Time column has a custom time format of [h]:mm:ss and the formula in cell G2 is:

=SUM($F$2:F2)

which is copied down to give the cumulative time for all song durations. Adding the [H] allows the Hour to show when the cumulative time adds up to over an hour.

Related Posts Plugin for WordPress, Blogger...