This post is going to show you all the ways you can convert minutes into hours in Google Sheets.
Sometimes your dataset will not contain the items in the exact format you need and you will have to do some manipulation to extract the results you want.
This is the case with time conversion. If the time attribute in your dataset is measured in minutes, but your analysis is based on hourly observation, then you must convert the time data to minutes.
Fortunately, there are various options from which you can choose to convert minutes to hours in Google Sheets.
This post will explore the following methods for converting minutes to hours in this post.
- Using a Simple Formula
- Using the CONVERT Function
- Using a Custom Date and Time Format
- Using the TEXT Function
- Using the QUERY Function
- Using the QUOTIENT and MOD Functions
- Using the TIME Function
Use the link above to get your copy of the example workbook to follow along.
Convert Minutes to Hours with a Simple Formula
The simplest and easiest way to convert minutes time value into hours is to divide it by 60 since an hour equals 60 minutes.
= B2 / 60 = DIVIDE ( B2, 60 ) = ARRAYFORMULA ( B2:B7 / 60 ) = ARRAYFORMULA ( DIVIDE ( B2:B7 / 60 ) )
Any of these above formulas will give you the hour equivalent of the minute time value.
= ROUNDUP ( B2 / 60, 2 ) = ARRAYFORMULA ( ROUNDUP ( B2:B7 / 60, 2 ) )
Use the above syntax to reduce the number of decimal places in your results.
Convert Minutes to Hours with the CONVERT Function
The CONVERT function allows you to change the measurement unit of numeric values.
= CONVERT ( value, start_unit, end_unit )
valueis the number whose unit of measurement you want to change.
start_unitis the number’s current unit of measurement.
end_unitis the unit of measurement to which you want to convert the number.
end_unit arguments need to be surrounded by quotation marks.
= CONVERT ( B2, "min", "hr" ) = ARRAYFORMULA ( CONVERT ( B2:B7, "min", "hr" ) )
The CONVERT function will also return decimal numbers like when you divide with 60.
= ROUNDUP ( CONVERT ( B2, "min", "hr" ), 2 ) = ARRAYFORMULA ( ROUNDUP ( CONVERT ( B2:B7, "min", "hr" ), 2 ) )
You can wrap the CONVERT function inside the ROUNDUP functions to reduce the number of decimal places.
You can also add the ARRAYFORMULA function to create a single formula that returns your entire array of results.
Convert Minutes to Hours with a Custom Date and Time Format
Another way to turn minutes into hours is with a custom format.
If you want to use the Custom date and time format to convert minutes to hours, you must divide the minute time value by 60, then again by 24.
This is because there are 24 hours in a day and 60 minutes in an hour.
Note: This method will only work when you have less than 1440 minutes or 1 day.
When you divide a number by 60 and apply a Custom date and time format, it returns the 24-hour time format equivalent.
Further division by 24 allows the Custom date and time format applied to return the result in the
hh:ss:mm duration format.
Apply your Custom date and time formats option using the Hour and Minute option in the Custom date and time formats dialogue box.
You can also use this Custom number format set-up to achieve the same result.
= ARRAYFORMULA ( B2:B7 / 60 / 24 )
After you apply the formatting to one cell, you can wrap your division equation in an ARRAYFORMULA using the above syntax.
With this, you can apply the formatting and carry out the calculation to the whole range at once.
Convert Minutes to Hours with the TEXT Function
= TEXT ( B2 / 60 / 24, "hh:mm" ) = ARRAYFORMULA ( TEXT ( B2:B7 / 60 / 24, "hh:mm" ) )
To convert minutes to seconds you need to divide the minute time value by 60, then by 24.
Take note that the TEXT function changes numeric values to string or text values.
Convert Minutes to Hours with the QUERY Function
= QUERY ( QUERY ( QUERY ( B1:B7, "SELECT B/60/24", 1 ), "FORMAT Col1 'hh:mm'", 1 ), "LABEL Col1 'Time (in hrs)'")
The QUERY function can help you convert minutes to hours. The syntax above follows a similar procedure as before.
The SELECT command divides the minute time value with 60 and 24, the FORMAT command applies formatting to the result of the division, and the LABEL command provides the column name.
Convert Minutes to Hours with the MOD and QUOTIENT Functions
You can calculate the number of complete hours and minutes by using a combination of the MOD and QUOTIENT functions.
The MOD and QUOTIENT functions carry out division operations but return different results.
= MOD ( dividend, divisor )
MOD returns the remainder from a division operation.
= QUOTIENT ( dividend, divisor )
QUOTIENT returns the result of a division operation, but without the remainder.
= QUOTIENT ( B2, 60 ) & ":" & MOD ( B2, 60 ) = ARRAYFORMULA ( QUOTIENT ( B2:B7, 60 ) & ":" & MOD ( B2:B7, 60 ) )
Combine both functions with the ampersand operator to convert minutes to the complete number of hours with the remaining minutes.
Convert Minutes to Hours with the TIME Function
= TIME ( hour, minute, second )
The TIME function takes the hour, minute, and second components as arguments and converts them to a time.
= TIME ( 0, B2, 0 ) = ARRAYFORMULA ( TIME ( 0, B2:B7, 0 ) )
When you set the hour and second arguments to zero, the function will convert the provided minute components to a duration format.
For any situation where you need to convert minutes to hours, you can use any of these methods to achieve the result.
Dividing the number of minutes by 60 or using CONVERT function will give you a decimal value for the hour value.
Other methods like the QUERY, TEXT, TIME, MOD, and QUOTIENT functions, or Custom date and time format will give you the exact hour plus remaining minute equivalent.
Do you have any other methods for converting minutes to hours? Let me know in the comments!