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.
You see, Excel can convert seconds easily with the TIME Function. All you have to do is format a worksheet cell with the proper time format.
The formula for Min:Sec in cell F2 is shown in this Function Arguments dialog box.
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.
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:
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.