Convert Seconds with the TIME Function in Excel

by Gregory on November 22, 2010

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.

Convert Seconds with TIME Function

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...

Comments on this entry are closed.

Previous post:

Next post: