Convert Seconds to a Time Format in Excel

by Gregory on August 26, 2010

Convert Seconds to a Time Format in Excel

The last several posts have shown how to convert and aggregate a given number of seconds that represent song duration. A simple example because song duration never exceeded one hour.

But what if your data has values greater than 3600 seconds? How would you convert those seconds into an hour:minute:second format?

Luckily, the previously mentioned functions: TIME, INT, MOD still work. However, the arguments get a little more complicated.

To simplify things we’ll look individually at the three TIME Function arguments: Hours, Minutes, Seconds. Going so far as to create a formula for each, then combining into one big formula.

A Short Review

The INT Function returns the integer portion of a division and the MOD Function returns the remainder. If I divide 7 by 2, then INT(7/2) = 3 and MOD(7,2) = 1. Two goes into seven, three times, with one left over.

Additionally, there are two conversion factors.

  • 1 hour = 60 minutes/hour x 60 seconds/minute = 3600 seconds
  • 1 minute = 60 seconds/minute = 60 seconds

The TIME Function Arguments

In the following example, the value in cell A2 is 3661 seconds, which is 1 hour, 1  minute and 1 second.

Hours Argument

Getting the number of hours is easy, just use A2/3600 for the INT Function argument.

Hours Argument

Minutes Argument

We need the remainder of A2/3600, which is MOD(A2,3600) = 61, but still have to convert these seconds to minutes. The answer is to divide MOD(A2,3600) by 60 and get the integer portion for the number of minutes.

Minutes Argument

In other words, 3600 goes into 3661 once, with 61 seconds left over. The MOD function gives us the 61 seconds. The INT function argument then reduces to INT(61/60), which gives us one (1), because 60 goes into 61 once.

Seconds Argument

MOD(A2/3600) gives us 61 seconds, so we need to divide by 60 and get the remaining seconds by using the MOD function again.

Seconds Argument

Since the inside MOD function equals 61 seconds, MOD(61,60) gives the us one (1), because after you divide 61 by 60 there is one (1) second left.

TIME Function

Putting all of these together inside the TIME Function makes the formula rather large.

TIME Function using Hours

Yet knowing how the functions work in each argument makes things a little more understandable.

Another TIME Function Example

Since 1:01:01 was easy math, but lousy use of too many 1’s I’ll use the value in cell A3, 7510, as a second example.

  • Hours: INT(7510/3600) = 2 hours
  • Remainder: MOD(7510, 3600) = 510 seconds
  • Minutes: INT(510/60) = 5 minutes
  • Seconds: MOD(510,60) = 10 seconds
  • TIME(2,5,10) = 2:5:10

Notice the Remainder of the first division (7510/3600) is the central argument for Minutes and Seconds.

Related Posts Plugin for WordPress, Blogger...

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: