HomeGoogle DocsSpreadsheet

Group and Sum Time Duration Using Google Sheets Query

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

I have two different methods in front of me to group and Sum time duration using Google Sheets Query.

In that the first method is simple but some manual formatting is required.

If you use the Query aggregation function SUM to sum a time duration column, the formula would return #VALUE! error.

Here is an example.

FORMULA # 1:

=query(A1:E,"Select Sum(E)",1)
SUM returns error in Query that involves time calculation

The tooltip (please refer the screenshot) shows the reason for the error, i.e., “AVG_SUM_ONLY_NUMERIC”.

So obviously you will get the same error in grouping also.

So before going to group and Sum time duration using Query, you must learn to solve the above error.

To Sum time duration in Google Sheets Query, whether it’s with or without grouping, you must first convert the values in the time duration column to numeric.

There are two ways that you can do this in Query. Just read on to get that two formulas.

Sum Time Duration Using Query – Formula Options

Query Formula + Manual Formatting

This is the simplest method.

If you have no issue in formatting the duration column (column E) to number, you can follow this.

First select the time duration in E2:E8 or the entire range E2:E. Then go to the Format menu and apply Number > Number.

The same formula, that I’ve shared above, will then begin to work correctly. But the output will be in number. That also we can solve.

I have the above Query formula (formula # 1) in cell G2 and see the output in number format.

Sum time column - Query formula 1

Just select the cell G3 and apply Format > Number > Duration.

That’s all that you want to do to Sum time duration using Query in Google Docs Sheets.

Query Formula to Sum Time Duration (No Manual Formatting)

For those who do not want to manually format the duration column, here is the solution.

Step # 1:

First we must convert the time duration in E2:E using a formula.

To convert time duration to time values or numbers you must convert the hours, minutes and seconds to numbers. How?

This generic formula explains that.

Hour/24+Minute/1440+Second/86400

How to apply this generic formula that converts time duration to number in Google Sheets?

Please find that relevant formula below.

We want to convert the duration in an array, i.e. E2:E8. So we must wrap the formula in Sheets with the ArrayFormula function.

=ArrayFormula(hour(E2:E8)/24+minute(E2:E8)/1440+second(E2:E8)/86400)

But I am not going to use this in a limited array like E2:E8. Instead, I wish to use it in an entire range E2:E.

So the formula to convert time duration to time values (numbers) will be as follows.

={"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}

In this formula, the LEN controls blank rows and additionally added the column label “Duration” in the first row.

Time to time value or number

Step # 2:

Now instead of column E, you can use this virtual column in your Query. I mean instead of A1:E, use this virtual range.

Generic Formula:

{A1:D,the virtual column E}

Here is that new (virtual) range.

={A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}}

Here is the Query formula to sum the time duration.

FORMULA # 2:

=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Sum(Col5)",1)

Please note that, when using virtual data in Query, the column identifier must be column number, not column letter. So I have replaced Sum(E) with Sum(Col5).

The above formula returns number, not duration. So you must format the result column to duration by clicking Formnat > Number > Duration.

This method I have explained in one of my advanced Query tutorials – Query to Calculate Hours Worked in Week Wise in Google Sheets.

How to Group and Sum Time Duration Using Query in Sheets

This part is simple. It’s all about how you use the Query group by clause with the above formulas.

To group and Sum time duration, we can either use the formula # 1 or formula # 2 above.

Of course, you must include the group by clause in those formulas.

Here is that two formulas to group and sum time duration using Query.

Query Formula with Manual Formatting

In this example, I want to calculate the total duration spent on the events “Planning” and “HSE”.

That means column B is the grouping column in Query and column E is the Sum column.

First, format the column E (the range E2:E) to number.

Sample data contains event and time spent

Here is that formula that groups the “Event” column and sum “Duration”.

=query(A1:E,"Select B, Sum(E) where A is not null Group By B",1)

In the following screenshot, I have underlined those new changes compared to Formula # 1.

The range E2:E and the result range H3:H4 has been manually formatted to duration.

Group and Sum time Duration using Query - Option 1

Query Formula Without Manual Formatting

Here no doubt we can use Formula # 2. We must modify the formula 2 to include the grouping.

Please refer to the screenshot below to understand what changes I have made.

=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Col2,Sum(Col5) where Col1 is not null group by Col2",1)
Group and Sum time Duration - Option 2

Here also you must format the range H3:H4 to duration.

Note: On the above image please ignore the format clause on the formula. That’s not required. The same has already been removed from my formula above.

IMPORTANT UPDATE

In all the above Query formulas for the aggregation of duration values, wherever applicable, the virtual range {"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))} can be replaced by either of the below two formulas.

Alternative # 1:

{"Duration";ArrayFormula(if(len(E2:E),timevalue(E2:E),))}

Alternative # 2:

{"Duration";ArrayFormula(if(len(E2:E),value(E2:E),))}

Additional Resources:

  1. How to Use DateTime in Query in Google Sheets.
  2. Google Sheets: The Best Overtime Calculation Formula.
  3. How to Filter Timestamp in Query in Google Sheets.
  4. How to Extract Date From Time Stamp in Google Sheets.
  5. Filter Out Blank Columns in Google Sheets Using Query Formula.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Excel Formula

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...
Excel Formula

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...
Excel Formula

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...
Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

15 COMMENTS

  1. Hi Prashanth,

    Thanks a lot.

    Here it is:

    Link copied, then removed – Admin.

    I hope I’ve shared it correctly.

    Thanks.
    .S.

    • Hi, Sara,

      Sorry for the late reply. I was offline for two days.

      Now to your problem.

      I could find two issues.

      1. Your sample data is in the ottobre tab and the formula is in another tab. So in the formula correctly refer to the source. The range used in your formula is ottobre!A1:C and D1:D. You have missed ottobre! with D1:D.

      2. You may please see my formula. I have used the duration column range from the second row skipping header. So you should use ottobre!D2:D instead of D1:D.

      So the formula would be;

      =Query({ottobre!A1:C\{"per progetto - OTTOBRE";ArrayFormula(if(len(ottobre!D2:D);(hour(ottobre!D2:D)/24+minute(ottobre!D2:D)/1440+second(ottobre!D2:D)/86400);))}};"Select Col1,Sum(Col4) where Col1 is not null group by Col1 format Sum(Col4) 'HH:MM:SS'";1)

      Note for other readers: This formula is for Italian locale.

      Best,

      • Hi Prashanth,

        I must admit I’m rather ashamed, I changed the reference to only the first occurrence… sorry for disturbing you for what I could have done on my own with some more care. Thanks anyway!

        Only one further question: why do I have to manually set the Format of the duration column to Duration? If I don’t do that then I get times instead but I thought that the format Sum(Col4) ‘HH:MM:SS’ part of the formula meant to spare having to set the format manually. Am I wrong?

        Thanks for your time and patience.
        .S.

  2. Hi Prashanth,

    Sorry to bother you again, I’m not sure you can give me some hints on this.

    I would now like to reference a data set on another sheet of the same spreadsheet so as to use a sheet for the data and another for the queries. I have done it on another spreadsheet and other data and it worked but on this, I get a #REF! error.

    My formula is:

    =Query({'Produzione AGOSTO'!A1:C\{"per progetto - AGOSTO";ArrayFormula(if(len(D1:D);(hour(D1:D)/24+minute(D1:D)/1440+second(D1:D)/86400);))}};"Select Col1,Sum(Col4) where Col1 is not null group by Col1 format Sum(Col4) 'HH:MM:SS'";1)

    and Produzione AGOSTO is the name of the sheet inside the spreadsheet.

    The message I get related to the error is:

    “Error”
    “Function ARRAY_ROW parameter 2 has a mismatched row size. Expected: 999. Actual: 1001.”

    I looked around on the internet but couldn’t find a solution. Should I share my spreadsheet with you?

    Thanks!!

    .S.

  3. Hey, thanks a lot Prashanth, it works!

    Thanks for pointing me out to the other article, I understand I should have changed all , into ; to adjust to my locale.

    There are a couple of things I haven’t got clear and I would really appreciate if you’d be so kind as to explain to me.
    1. Query({A1:C\{ : why is there a backslash in the range? Also: why the range doesn’t have to extend enough to include the column I want to sum?

    2. Is the final ;0) or ;1) accounting for the no first row of labels vs yes the first row of labels?

    Thanks again! Really!

    .S.

    • Hi, Sara,

      First, let me answer your question number 2.

      Question 2:

      The use of 0 or 1 is actually to specify whether there is a header row (a row with labels) in your selected range. 0 represents 0 label row, 1 represents 1 label row, 2 represents two label rows and so on.

      Query Syntax:

      QUERY(data, query, [headers])

      Actually, the headers is an optional argument in Query. If you exclude specifying headers, Query will automatically detect the headers.

      Question 1:

      It’s regarding the use of backslash. If you have checked my article (link shared on my previous reply) regarding how to convert nonregional formulas (formula locale settings), you could understand this usage.

      Depending on your region, you may want to replace a , in my formula with ; as the separator within a function. As far as I know in most of the EU countries ; replaces , as the separator.

      Then your question is why I have replaced a , in the first part of the Query with \.

      The answer is within curly brackets, the , should be replaced with a \ not a ;

      Best,

      • Thanks.

        I had understood the ; vs , part of the article but I hadn’t clear about the use of \ replacing , inside {}. Now I know, thanks.

        .S.

      • Hi Prashanth,

        Thanks for your reply and for sharing the example datasheet with me.

        I figured out the problem with the sum I had: the format of the column containing the sum would go to “Automatic” and it would be interpreted as Time instead of Duration, so it would show me 15:30 (time) instead of 39.30 (duration).

        Once I selected Duration as the format of the Column containing the sum the numbers showed added up correctly.

        Best,

        .S.

  4. Hi there.

    Thanks for the article, it’s very clear and is exactly what I need: However, I can’t make the formula to work: it gives me a #ERROR! with a generic Formula parse error message.

    Here is my formula

    =Query({A1:D,{"Duration";ArrayFormula(if(len(D1:D),(hour(D1:D)/24+minute(D1:D)/1440+second(D1:D)/86400),))}},"Select Col1,Sum(Col4) where Col1 is not null group by Col1 format Sum(Col4) 'HH:MM:SS'",1)

    where the Duration values I want to add are in Column D.

    Any help you may give me? Thanks!!

    • Hi, Sara,

      Can your Share your ‘Locale’ which you can find under your File menu Spreadsheet settings. Also, I want to know the following;

      1. The number of columns in your dataset.
      2. Whether the first row contains column names (field labels).
      3. Values in each column. I mean the column contains text, date, or time.

      Best,

      • Hi Prashanth,

        Thanks for your reply!

        My “Locale” is Italy.

        As to replies to your questions:

        1. My dataset has 5 columns

        2. The dataset I’m working with is a test dataset and doesn’t have field labels. The one I will be interested in applying the formula to will have field labels in the first row.

        3. The first 3 columns contain text (data fed by another sheet). The 4th contains Duration (and it’s the one I want to add the values of). The 5th column contains a Date.

        Also, if I follow your option n. 1, with manual formatting, it works and I get the results I need. But it would be better for me and my team to be able to apply a formula that works always.

        Thanks!

        • Hi, Sara,

          My original formula after converting to your Locale.

          =Query({A1:D\{"Duration";ArrayFormula(if(len(E2:E);(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400);))}};"Select Col2,Sum(Col5) where Col1 is not null group by Col2 format Sum(Col5) 'HH:MM:SS'";1)

          The same formula tuned for your dataset (for the fourth column as the ‘Duration’ column).

          First Row Contains Labels:

          =Query({A1:C\{"Duration";ArrayFormula(if(len(D2:D);(hour(D2:D)/24+minute(D2:D)/1440+second(D2:D)/86400);))}};"Select Col1,Sum(Col4) where Col1 is not null group by Col1 format Sum(Col4) 'HH:MM:SS'";1)

          The first Row Doesn’t Contain Labels:

          =Query({A1:C\{ArrayFormula(if(len(D1:D);(hour(D1:D)/24+minute(D1:D)/1440+second(D1:D)/86400);))}};"Select Col1,Sum(Col4) where Col1 is not null group by Col1 format Sum(Col4) 'HH:MM:SS'";0)

          This will work for you. If not, please let me know, I will share my Sheet with you.

          Also, please read – How to Change a Non-Regional Google Sheets Formula.

          Best,

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.