Search code examples
if-statementgoogle-sheetstimegoogle-sheets-formulaaverage

How to convert minutes to hours in google sheets?

I have some data expressed in minutes. I retrieve the average value using this formula:

=QUERY(A3:D1074,"select C, avg (D) group by C")

An example value I receive is 351.333333333333 (minutes).

Then I convert it to hours using this formula:

=QUOTIENT(N3,60) & ":" & IF(LEN(MOD(N3,60))=1,0,"") & MOD(N3,60)

The result i get is 5:51.3333333333333 (hours:minutes)

Now I'm unable to make a graph using the values expressed in hours. I see invalid type error when I enter the data to be used in graph.

How can I use convert the time values from minutes to hours and still be able to use it in a graph?

enter image description here

enter image description here

Solution

  • try:

    =TEXT(E2/24/60/60; "mm:ss")

    enter image description here

    or directly:

    =INDEX(TEXT(QUERY(A3:D, "select avg(D) group by C label avg(D)''")/24/60/60, "[mm]:ss"))

    enter image description here

    UPDATE:

    enter image description here

    enter image description here