CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Peter Varga
Peter Varga

Reputation: 15

Google Sheets Query multi condition sum of time duration

I am using Google sheets to record rental data. The table looks like this:

[Client Name] [Product Code] [Product Name] ... some irrelevant columns ... [Rental Start] [Rental End] [Rental Duration] ... some more irrelevant columns

For the [Product Code] column, I need to check if the [Client Name] has over 3 hours of cummulative [Rental Duration] on the same day with the same [Product Name].

I tried using:

=query($A$3:$O,"select sum(hour(M)+min(M)/60) where Year(J)="& year(J117) & " and Month(J) ="& month(J117)-1 & " and day(J) = " & day(J117) & " ",-1)

But the sum() function doesn't accept the time duration format or the hour() function.

I need to use the result of this query as part of a bigger query, where depending on the result I search for a subscription or an other product code.

How can I sum up the time duration in column M?

Upvotes: 1

Views: 1779

Answers (1)

ttarchala
ttarchala

Reputation: 4567

Try converting the column M into explicit numeric format. You can do that using the N() function. As you need the output in hours, you need to multiply this number by 24 (durations as numbers' unit measure is 1 day).

=query({$A$3:$L, arrayformula(n($M$3:$M)*24), $N$3:$O},
       "select sum(M) where ...")

Obviously this is untested, share your spreadsheet if you want a chance for somebody to help with debugging.

Upvotes: 1

Related Questions