My task is to take a quantity of seconds and convert to a minutes:seconds format. For example, I need 274 seconds to become 4:34 in my spreadsheet. The first step, which I’ll cover in this post, is to get the number of minutes.
As I’ve mentioned before, three Functions will do this for me: QUOTIENT, ROUNDDOWN, and INT.
The QUOTIENT Function
The QUOTIENT Function comes standard in newer versions of Excel: 2010, 2008, and 2007.
In Excel 2003 you have to add the Analysis ToolPak Add-In. To do so, select Tools » Add-Ins, then select Analysis ToolPak, and click OK.
There are two arguments for the QUOTIENT Function: Numerator and Denominator.
I want to divide Time (Sec) by 60 to come away with the number of minutes. As you can see, Numerator is Time (sec) and Denominator is 60.
The Function Arguments dialog box shows the answer to be four (4), which is how many whole minutes are in 274 seconds. Precisely the answer I want.
The ROUNDDOWN Function
The ROUNDDOWN Function has two arguments: Number and Num_digits.
Number is Time(Sec) divided by 60. Num_digits is zero (0).
Again, the Function Arguments dialog box shows the answer is four (4).
The INT Function
The INT Function has one argument: Number.
Number is Time(Sec) divided by 60.
Once again the answer is four(4).
Which Function to Use?
It’s wise to check how each function operates for the conditions you have. In this case, I created a column of numbers, representing seconds, ranging from zero to 240 and put each formula listed above in the next three columns. All performed as expected.
The QUOTIENT Function is easy to use with two simple arguments: Numerator and Denominator, but it’s not a standard formula before Excel 2007.
The ROUNDDOWN Function is the hardest to use because it takes longer to type, and has more stuff in the arguments.
The INT Function is the easiest to use, shortest to type, and only one argument. Winner, hands down.