Google Sheets: Query data with HH:MM format

Question

Which query should I use to calculate Total shift time and Total task time grouped by Shift?

Google Sheets query doesn't allow me to sum Col2 or Col3 because it's formatted into HH:MM (I can't change it to number because source data is HH:MM).

may we assume that no shift begins before midnight and ends after? — Jeremy Kahan
It will sometimes. I was thinking to add 1 (+24:00h) If it happens — Tes

Tom Sharpe Tom Sharpe · Accepted Answer · 2020-04-12T21:21:51

This is the basis of a solution, but I had to format the Query columns as time manually and you would have to add 1 as you say to column C if the shift split across midnight:

=ArrayFormula(query({A2:A,to_pure_number(B2:B),to_pure_number(C2:C)},"select Col1,max(Col3)-min(Col2),sum(Col3)-Sum(Col2) where Col1 is not null
 group by Col1 label Col1 'Shift',max(Col3)-min(Col2) 'Total',sum(Col3)-Sum(Col2) 'Task' "))

Here is a suggestion for handling shifts that go across midnight to the next day. It's necessary to add 1 to both time columns to get the right result. This assumes that the tasks are entered in the right order in each shift, or at least that the first task is entered first for each shift:

=ArrayFormula(query({A2:A,to_pure_number(B2:B+(B2:B<vlookup(A2:A,{A2:A,B2:B},2,false))),
to_pure_number(C2:C+(C2:C<vlookup(A2:A,{A2:A,B2:B},2,false)))},"select Col1,max(Col3)-min(Col2),sum(Col3)-Sum(Col2) where Col1 is not null
 group by Col1 label Col1 'Shift',max(Col3)-min(Col2) 'Total',sum(Col3)-Sum(Col2) 'Task' "))

Google Sheets: Query data with HH:MM format

1 Answers