Converting seconds into a minutes:seconds format is rather simple once you know the component parts of the formula. We'll use three functions. (click to review prior post)
TIME(hour,minute,second)
INT(number)
MOD(number,divisor)
For the TIME Function, the three arguments are:
hour = 0
minute = INT(number)
second = MOD(number, divisor)
Which leaves us with:
TIME(0, INT(number), MOD(number,divisor))
Using 274 seconds as an example, for the INT argument: number = 274/60. The MOD arguments: number = 274, and divisor = 60. Therefore:
=TIME(0,INT(274/60,MOD(274,60))
You can see this applied below, with H2 used as a cell reference for 274, and 60 as the conversion constant.
The TIME Function Argument Dialog Box shows that the function returns 0.003171296, which is the time serial number, and the formula result is 4:34.
When faced with a column of data that shows Time in seconds, the TIME function is useful when combined with the INT and MOD functions. The INT function extracts the number of minutes when the Time(Sec) is divided by 60 seconds per minute, and the MOD function gives the remaining seconds, given the number of seconds and the divisor 60.
In this particular spreadsheet the Hour argument will always be zero because the data happens to be song duration from an iTunes playlist. In a future post I will show and explain the arguments for when Time in seconds is greater than one hour.
The next post will deal with accumulating the TIME data.