How to convert a number into hours in Google Sheets

15
votes

1950 minutes is 32.5 hours. How do I calculate this using a formula in Google Sheets?

I am logging the number of minutes that I take on completing tasks and need a way of converting these numbers into the number of hours in Google Spreadsheets.

I have a column that contains rows of minutes, 1 row per task. This sum total of this column then needs to be added together, then converted into hours.

6
google-sheetsgoogle-sheets-formula

6 Answers

24
votes

Here is a function that converts minutes in hour:minutes,

=if( A1 = 0 ; "" ; concatenate( quotient( A1 ; 60 ) ; ":" ;MOD(A1  ; 60) ))

62 --> 1:2

11
votes

Use convert function to convert a numeric value to a different unit of measure

=CONVERT(A1; "mn"; "hr")

Documentation

5
votes

Pierre's answer is mostly correct. However, it does not properly output the time if the number of minutes is below 0. If you need to handle a negative time amount, use the following formula instead:

=CONCATENATE(IF(AND(A1 < 0, A1 > -60), "-", ""), QUOTIENT(A1, 60), ":", TEXT(MOD(ABS(A1), 60), "00"))
5
votes
  1. Add up all the values using the function =SUM(number1,number2,number3,number4,...). Example: =SUM(A1:A200) adds up all the numbers from A1, A2, A3 ... until A200.
  2. Divide the total number of hours (it's located in the field with the =SUM function) by 60 (60 minutes in 1 hour, of course) with =DIVIDE(number1,number2) Example: =DIVIDE(A201,60). In case you put the =SUM function in field A201, you divide the result of that field by 60.
4
votes

Here is a function that converts minutes in hour:minutes: 

=CONVERT(A1,"mn","hr")/24

Where A1 is the section you want to convert

2
votes

I've improved it:

  • 123 to proper 2h 3min
  • Do not show insignificant 0.
    • not show "0 mins" (number is completely divisible by 60) e.g. 120mins to 2h and NOT 2h 0min
    • not show "0h" (number is less than 60) e.g. 40min to 40min and NOT 0h 40min
  • To use just replace A1 with the cell number of the cell that contains mins.
=IF (A1=0, "", CONCATENATE ( IF(QUOTIENT(A1,60)=0, "", CONCATENATE (QUOTIENT(A1,60),"h ")  ), IF(MOD(A1,60)=0,"",CONCATENATE (TEXT(MOD(A1,60),0),"min"))))