How to convert minutes to hours in google sheets?-CodePudding

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][1]][1][enter image description here][2] [1]: https://i.stack.imgur.com/V1pkE.png [2]: https://i.stack.imgur.com/mU8Rx.png

CodePudding user response：

The data you get after converting to hours is text, because you concatenate numbers (hours, minutes) with text (:). This can be easily checked by using the TYPE() formula on the result of your conversion: it returns 2, meaning it is text data (1 would be numerical data).

I would suggest simply dividing the data in minutes by 60 and use that in the graph, it should work as it will be numerical data.

CodePudding user response：

try:

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

or directly:

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

UPDATE: