How to deal with month grouping and sum of hours of these months in Google Sheets?-CodePudding

I'm having trouble filtering a column by month/year and counting the unique values. I started trying with ARRAYFORMULA, then with QUERY, but without success.

A	B	C	D	E	F	G
Date	Start Time	End Time	Duration	Month	Worked Days	Total Duration
01/06/2022	05:06	08:56	3h50min	06/2022	9 days	31h47min
02/06/2022	05:08	08:43	3h35min	07/2022	5 days	24h36min
02/06/2022	15:25	16:57	1h32min
03/06/2022	05:13	08:24	3h11min
04/06/2022	05:11	09:24	4h13min
06/06/2022	13:05	14:36	1h31min
07/06/2022	05:20	08:27	3h07min
08/06/2022	05:08	08:52	3h44min
09/06/2022	05:09	09:17	4h08min
10/06/2022	05:11	08:07	2h56min
01/07/2022	05:10	09:43	4h33min
02/07/2022	05:23	07:43	2h20min
04/07/2022	05:08	07:41	2h33min
04/07/2022	20:57	21:59	1h02min
05/07/2022	05:13	09:54	4h41min
06/07/2022	05:10	09:38	4h28min
06/07/2022	15:11	18:05	2h54min
06/07/2022	20:00	22:05	2h05min

Columns from A to D is what I have. Columns from E to G is what I expect.

One of the problems is that sometimes we have the day being repeated.

CodePudding user response：

try:

=ARRAYFORMULA(QUERY({TEXT(A3:A; "mm/e")\ 
 IF(COUNTIFS(A3:A; A3:A; ROW(A3:A); "<="&ROW(A3:A))=1; 1; 0)\ C3:C-B3:B}; 
 "select Col1,sum(Col2),sum(Col3) where Col3>0 
  group by Col1 label sum(Col2)'',sum(Col3)'' 
  format sum(Col3)'[h]\hmm\min'"))