How to Convert Minutes into Hours and Minutes?
If you want to convert “minutes” into the format of “hours and minutes” in Excel, you can automate the process with calculations. For example, this method is useful when you want to display 100 minutes as “1 hour 40 minutes.” This article explains how to display minutes in the “hours and minutes” format.
Calculation Method to Convert Minutes into Hours and Minutes
To convert minutes into hours, divide the total minutes by 60, taking the quotient as “hours” and the remainder as “minutes.” In Excel, you can use a combination of functions to perform this calculation.
Example: Converting 100 Minutes into 1 Hour 40 Minutes
In the table below, minutes are entered in cell A1, and the result in “hours and minutes” is displayed in cell B1.
| A | B | |
|---|---|---|
| 1 | 100 | =INT(A1/60) & ” hour(s) ” & MOD(A1, 60) & ” minute(s)” |
This formula works as follows:
- INT(A1/60): Calculates the quotient of A1 divided by 60 (the hour part).
- MOD(A1, 60): Calculates the remainder of A1 divided by 60 (the minute part).
- & ” hour(s) ” &: Concatenates the calculated values with the text “hour(s)” and “minute(s).”
Steps
- Enter the total minutes in cell A1 (e.g., 100).
- Enter the following formula in cell B1: =INT(A1/60) & ” hour(s) ” & MOD(A1, 60) & ” minute(s)”
- Press Enter, and cell B1 will display “1 hour(s) 40 minute(s).”
Result
Using this formula, 100 minutes will be displayed as “1 hour(s) 40 minute(s).” Similarly, any number of minutes can be automatically converted into the “hours and minutes” format.
Application: Display Minutes in Time Format (h:mm)
In Excel, you can also display minutes directly in the “hours:minutes” format by using the custom time format “[h]:mm.” This is useful for showing a total number of minutes in the time format.
To display the minutes in a time format, use the following formula:
=A1/1440
This formula converts the minutes into “days” by dividing by 1440 (60 minutes × 24 hours). Enter 100 in cell A1, format the cell as “[h]:mm,” and it will display “1:40.”
Steps
- Enter the total minutes in cell A1 (e.g., 100).
- Enter the following formula in cell B1: =A1/1440
- Select cell B1, right-click, and open “Format Cells”.
- Under “Category,” select “Custom” and set the format as “[h]:mm.”
- Press Enter, and cell B1 will display “1:40.”
Summary
To convert “minutes” into “hours and minutes” in Excel, the INT function and MOD function are useful for separately calculating hours and minutes. Alternatively, by setting the cell format to “[h]:mm,” you can automatically display the time in “hours:minutes” format.