I’ve been looking at converting a number of seconds into a minutes:seconds format. My example is converting 274 seconds by using the INT Function to extract 4 minutes and the MOD Function to extract 34 seconds.
In this post I’m using those values inside the TIME Function.
The Time Function has three arguments: Hour, Minute, Second.
This particular spreadsheet contains song duration in seconds so the Hour argument will always be zero.
The Minute and Second arguments come from the INT and MOD columns, respectively. The INT Function was used to extract the minutes and the MOD Function was used to extract the seconds.
The result of the calculation is 0.003171296, which is the time serial number and is displayed in the cell as 4:34 because the cell formatting is set to custom type m:ss.
In my next post I’ll insert the INT and MOD Functions in the TIME Function.
Or you could have just done =Time(0,0,A2) and thus avoiding doing the work of the Time function for it…
Or you could directly divide the seconds by 86400 (number of seconds in a day) to get the time serial number.
You are perfectly correct. Using the Time Function is great if the number of seconds is less than 32767. And dividing by 85,400 is even better, if you explain how to change the cell formatting to see a hours:minutes format. Both methods are covered in other post I have written elsewhere. This post was one of a series that logically followed the INT and MOD functions to parse time elements.
I find that it helps to keep my subjects small to keep my readers interested and focused.