ExcelInsider Team
In Excel, a simple division formula can convert seconds to minutes. However, the result can display as a decimal number and you might want a different format for a cleaner look. Several Excel functions and cell formatting allow you to display the converted time in your preferred format.
➤ Select a cell adjacent to the original time cell and enter the following formula:
=CONVERT(D2,”sec”,”mn”)
➤ Here, D2 is the cell containing the time in seconds. Change it based on your dataset.
➤ Press Enter and drag it down using the fill handle (tiny + sign on the bottom-right corner of the cell).
With this formula, Excel converts seconds into decimal minutes. If you want to display minutes and seconds in a different format without a decimal, you can use functions like TEXT, INT, and MOD. This article covers all the methods of converting seconds to minutes.
Basic Division Formula to Convert Seconds into Decimal Minutes
To demonstrate the conversion methods, we’re using a tracking sheet for a swimming event. Our columns feature the participants’ names, prominent strokes, positions, and recorded times. Column D contains the initial time in seconds and we’ll put the converted time in minutes in column E.
As 1 minute equals 60 seconds, dividing the given seconds by 60 gives the time in minutes. However, it displays time as a decimal number where the whole number represents time in minutes and the seconds are the fractional part. Here’s how the process goes:
➤ Choose the first cell in the final time column and enter the following formula:
=D2/60
➤ Change the cell reference D2 for the first cell of your dataset containing the seconds.
➤ Click Enter and drag the formula down to the remaining cells using the fill handle.
Converting Seconds to Minutes Using Division and Custom Time Formatting
If you prefer to display the converted time as minutes and seconds instead of decimal numbers, try this effective method. Here, we’ll convert the seconds into days and then format the cells in as minutes:seconds. For this, follow the steps given below:
➤ Enter the following formula in a cell neighboring the original time cell:
=D2/86400
➤ Instead of D2, use the appropriate cell reference containing the time in seconds in your dataset.
➤ Press Enter and use the fill handle to autofill the remaining cells.
➤ As Excel displays the results as numbers, we need to format them as time. For this, select all the cells you want to convert and go to the Home tab.
➤ Navigate to the Number group on the main ribbon and click on the launcher icon (downward arrow sign). Or, press the keyboard shortcut CTRL + 1 .
➤ In the Format Cells dialog box, go to the Number tab and select Time from the Category group.
➤ You can choose the hh:mm:ss format to display time as 00:02:18.
➤ To show the minutes and seconds only, choose the Custom option instead and enter mm:ss in the Type box.
➤ This way, Excel displays the converted time as 02:18. Here’s the final result:
Seconds to Minutes Conversion with the CONVERT Function
To directly convert time units, we use the CONVERT function. By specifying the units you want to convert, you can create a formula to directly convert seconds to minutes. Here’s how:
➤ Select an appropriate cell in the final time column and insert the formula given below:
=CONVERT(D2,”sec”,”mn”)
➤ Replace D2 with the correct cell reference containing the time in seconds.
➤ Click Enter and drag the formula down to the rest of the cells.
Using the TEXT Function to Convert Time Units
With the TEXT function, you can directly specify a time format for the converted time units. However, the TEXT function changes the cell format to a text string instead of a time.
Therefore, you won’t be able to correctly perform any time-related calculations with the resulting data. Below are the steps to convert seconds to minutes with it:
➤ In a cell adjacent to the first cell of your initial time column, type the following formula:
=TEXT(D2/86400,”mm:ss”)
➤ Instead of D2, put the cell reference containing the initial time in your dataset. Also, you can choose a different time format format such as hh:mm:ss or m:ss instead of mm:ss.
➤ On your keyboard, press Enter. Drag the formula down with the fill handle.
Extracting Whole Minutes and Remaining Seconds in Text Format
To display the minutes and seconds as separate values or in a more descriptive text format, you can use INT and MOD. We’ll divide the time value by 60 and the INT function will return the whole minutes whereas the MOD function will give the remaining seconds.
Keep in mind that the result will be a text string, so time calculations in the future won’t be an option. Follow the steps given below:
➤ Start by selecting a cell in the final time column. Click on the cell and enter any of the given formulas:
Display Result As mm:ss
=INT(D2/60) & “:” & MOD(D2,60)
Display Result with Custom Text
=INT(D2/60)&” Minutes “&MOD(D2,60)&” Seconds”
➤ For both formulas, change the cell reference D2 to the one containing the time in seconds in your dataset.
➤ Click Enter and drag the formula down to autofill the rest of the cells.
Creating a Custom Formula with VBA Coding
Using a VBA macro is a dynamic and more reliable approach to convert seconds to minutes. It handles lengthy and complex datasets better and applies to future entries. Here’s the procedure:
➤ Look for the Developer tab in the top ribbon. If it’s not available, go to the File tab, click on More, and select Options.
➤ From the side column, choose Customize Ribbon and click on the Developer box. Press Ok.
➤ Now,choose the cells containing the time value you want to convert. As the Developer tab appears on the main ribbon, click on it, and choose Visual Basic.
➤ From the VBA editor, click on the Insert tab and select Module.
➤ In the new module box, paste the following code:
Sub ConvertSeconds_FormatChooser()
Dim userChoice As Integer
Dim cell As Range
Dim seconds As Double
Dim minutes As Double
Dim wholeMinutes As Long
Dim remainingSeconds As Long
Dim formattedText As String
' Prompt user for conversion type
userChoice = MsgBox("Choose format:" & vbCrLf & _
"Yes - Fractional minutes (e.g. 1.5)" & vbCrLf & _
"No - Time format (mm:ss)" & vbCrLf & _
"Cancel - Text format (e.g. 1 min 30 sec)", _
vbYesNoCancel + vbQuestion, "Convert Seconds")
' Check if user cancelled
If userChoice = vbCancel Then
For Each cell In Selection
If IsNumeric(cell.Value) Then
seconds = cell.Value
wholeMinutes = Int(seconds / 60)
remainingSeconds = seconds Mod 60
formattedText = wholeMinutes & " min " & remainingSeconds & " sec"
cell.Value = formattedText
End If
Next cell
ElseIf userChoice = vbYes Then
For Each cell In Selection
If IsNumeric(cell.Value) Then
seconds = cell.Value
minutes = seconds / 60
cell.Value = minutes
End If
Next cell
ElseIf userChoice = vbNo Then
For Each cell In Selection
If IsNumeric(cell.Value) Then
seconds = cell.Value
cell.Value = TimeSerial(0, 0, seconds)
cell.NumberFormat = "mm:ss"
End If
Next cell
End If
End Sub➤ Now, go to the Run tab and choose Run Sub/UserForm. You can also press F5 .
➤ From the Convert Seconds dialog box, and choose any of the given formats:
- Press Yes for fractional minutes (e.g., 2.3)
- Press No for time format mm:ss (e.g., 02:18)
- Press Cancel for text format (e.g., 2 min 18 sec)
➤ To save the changes, go to File and choose Save As. Navigate through your device folders to select the location to save the workbook.
➤ While saving the file, click on the arrow beside the Save as Type box and select Excel Macro-Enabled Workbook. Finally, press Save.
Frequently Asked Questions
How to convert seconds into days in Excel?
To convert seconds into days in Excel, use the formula:
=D2/86400
Replace D2 with the cell reference containing the seconds you’re converting. As there are 86400 seconds in a day (24×60×60), Excel will return the number of days when you enter this formula.
How to round seconds to minutes in Excel?
To convert and round seconds to the nearest minute, enter the following formula:
=ROUND(D2/60, 0)
Change the cell reference according to your dataset. This formula rounds up the given value to zero(0) places. You can also change the number of places you want to display.
How do you convert seconds to minutes in the Power Query?
Click on the Data tab, go to the Get & Transform Data group, and click on the From Table/Range icon. Select the data range with the time values. If prompted to create a table, click Ok. In the Power Query editor, go to Add Column >> Custom Column. Enter the following formula:
= Number.Round([YourColumnName] / 60)
Replace YourColumnName with the name of the column containing the time values. Finally, click Close & Load.
Wrapping Up
While a simple division formula can convert seconds into minutes, it doesn’t offer much control over what to display in your sheet. Among the alternatives, turning the seconds into days and formatting the cells later is the most convenient solution.
Using the TEXT, INT, and MOD functions has the drawback of turning the formatting into text. On the other hand, our VBA code offers better control and correct formatting options.
