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

*left over.*

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

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

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.

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.

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.

Comments on this entry are closed.

{ 1 trackback }