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?
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.
Getting the number of hours is easy, just use A2/3600 for the INT Function 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.
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.
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.