You can use the following formula in Excel to convert seconds to minutes and seconds:
This particular example converts the number of seconds in cell B2 into minutes and seconds.
For example, if the value in cell B2 is 440 then this formula will display 07:20 since 440 seconds is equivalent to 7 minutes and 20 seconds.
The following example shows how to use this formula in practice.
Example: Convert Seconds to Minutes and Seconds in Excel
Suppose we have the following dataset in Excel that shows the number of seconds that it took for various athletes to complete some task:
Suppose we would like to display each of the seconds in column B in minutes and seconds.
We can type the following formula into cell C2 to do so:
We can then drag and fill this formula down to each remaining cell in column C:
Column C now displays the number of seconds in column B in terms of minutes and seconds.
- 440 seconds is equal to 7 minutes and 20 seconds.
- 380 seconds is equal to 6 minutes and 20 seconds.
- 948 seconds is equal to 15 minutes and 48 seconds.
And so on.
How This Formula Works
Recall the formula that we used to convert seconds to minutes and seconds:
Here is how this formula works:
There are 86,400 seconds in one day. Thus, B2/86400 first calculates the number of days in cell B2.
For example, 440/86400 = 0.00509 days.
Next, the TEXT function displays this number of days in a format that uses mm to represent minutes and ss to represent seconds.
The end result is that 440 is shown as 07:20.
The following tutorials explain how to perform other common operations in Excel: