Group and Sum Time Duration Using Query in Sheets

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.

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.

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)

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:

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,

15 COMMENTS

Sara Polselli November 5, 2019 At 3:11 am

Hi Prashanth,

Thanks a lot.

Here it is:

Link copied, then removed – Admin.

I hope I’ve shared it correctly.

Thanks.
.S.

- Prashanth November 7, 2019 At 9:04 am
  
  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,
  
  - Sara Polselli November 7, 2019 At 6:31 pm
    
    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.
    
Sara Polselli November 3, 2019 At 8:23 pm

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.

- Prashanth November 4, 2019 At 1:20 pm
  
  Hi, Sara,
  
  Feel free to share a copy of the concerned Sheet with me. I will definitely try to solve the problem as early as possible.
  
  Best,
  
Sara Polselli October 8, 2019 At 1:31 am

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.

- Prashanth October 8, 2019 At 9:50 am
  
  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,
  
  - Sara Polselli October 8, 2019 At 5:59 pm
    
    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.
    
  - Sara Polselli October 9, 2019 At 2:43 am
    
    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.
    
Sara Polselli October 5, 2019 At 10:43 pm

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!!

- Prashanth October 6, 2019 At 8:39 am
  
  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,
  
  - Sara Polselli October 6, 2019 At 8:20 pm
    
    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!
    
    - Prashanth October 7, 2019 At 9:44 am
      
      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,
      
      - Sara Polselli October 8, 2019 At 1:52 am
        
        Sorry, I misplaced my previous reply and it probably makes the thread hard to follow 🙁
        
        Anyway, I have a problem. The first row is calculated erroneously at first. It did go right after some time or some change I made but I can’t figure out why just the numbers didn’t add up to the correct value whereas afterward they mysteriously did.
        
        This, in my test dataset. In my real one, again, numbers don’t add up correctly. Any ideas why?
        
        Thanks!
        
        
        Prashanth October 8, 2019 At 10:00 am
        
        Hi, Sara,
        
        I think it’s high time to share a demo Sheet with you! I have set the locale to Italy.
        
        https://docs.google.com/spreadsheets/d/1BpL1PdACg74KrzIIc-JO71apEB1n1Iq7eqgWKR4Wvgs/copy
        
        See if that helps?
        
        Best,

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

Limit Formula Expansion to a Specific Row in Google Sheets

3-D Referencing Structured Data Tables in Google Sheets

Case-Sensitive Running Count in Google Sheets

Fix Fractional Percentage Formatting Issues in Google Sheets

Automatically Pre-fill Google Forms from Google Sheets: A Step-by-Step Guide

How to Set Up Google Docs Forms: A Comprehensive Guide

How to Insert Drop-downs in Google Docs Documents

How to Create a Table and Pin and Unpin Header Rows…

How to Use Section Break in Google Docs

How to Split a Table in Google Docs

How to Create First Line Indent and Hanging Indent in Google…

Hierarchical Numbering Sequences in Excel

How to Easily Repeat a Sequence of Numbers in Excel

Create a Sequence of Dates at Every Nth Row in Excel…

Excel: How to Insert Subtotals Using a Dynamic Array Formula

Excel: Dynamic Arrays for Longest & Current Winning Streak

Days Between Weekday Names in Excel and Google Sheets

Display Month Names Only at Month Start (Excel & Google Sheets)

SUMIFS with XLOOKUP in Excel and Google Sheets

Counting XLOOKUP Results with COUNTIFS in Excel and Google Sheets

SORT and SORTBY – Excel Vs Google Sheets

Group and Sum Time Duration Using Google Sheets Query