I have the following big data in a table:
A B C D E
project ticketID date startTime endTime
MME 1 20-01-2017 12:00 13:00
MME 2 23-01-2017 12:00 14:00
VAN 1 24-01-2017 09:00 11:00
MME 1 24-01-2017 12:00 14:00
MME 3 02-02-2017 10:00 12:00
XMR 1 18-02-2017 11:00 12:00
MME 2 01-03-2017 13:00 14:00
I would like to know on a month basis how much hours in total were spent on which ticket.
The preferred outcome:
ticket month year total
MME-1 1 2017 3:00
MME-2 1 2017 2:00
VAN-1 1 2017 2:00
MME-3 2 2017 2:00
XMR-1 2 2017 1:00
MME-2 3 2017 1:00
You may use 2 querys to get the result like this:
A B C D E
ticket id year month total
MME 1 2017 1 3
MME 2 2017 1 2
MME 2 2017 3 1
MME 3 2017 2 2
VAN 1 2017 1 2
XMR 1 2017 2 1
The formula is:
=QUERY(
QUERY({A1:E8},
"select Col1, Col2, year(Col3), month(Col3)+1, hour(Col5)-hour(Col4)"),
"select Col1, Col2, Col3, Col4, sum(Col5) group by Col1, Col2, Col3, Col4
label Col1 'ticket', Col2 'id', Col3 'year', Col4 'month', sum(Col5) 'total'")
Edited by Mark:
I had to rewrite your formula since comma's give me an error I had to use semicolons. Also I enhanced your formula to give the result I was wanting.
=QUERY(QUERY({A:E\ARRAYFORMULA(IF(ISBLANK(A:A);"";A:A&"-"&B:B))};"SELECT Col1, Col2, Col6, YEAR(Col3), MONTH(Col3)+1, HOUR(Col5)-HOUR(Col4)");"SELECT Col3, Col5, Col4, sum(Col6) GROUP BY Col3, Col1, Col2, Col4, Col5 ORDER BY Col5 LABEL Col3 'ticket', Col5 'month', Col4 'year', sum(Col6) 'total'")
Formatted:
=QUERY(
QUERY(
{A:E\ARRAYFORMULA(IF(ISBLANK(A:A);"";A:A&"-"&B:B))};
"SELECT Col1, Col2, Col6, YEAR(Col3), MONTH(Col3)+1, HOUR(Col5)-HOUR(Col4)"
);
"SELECT Col3, Col5, Col4, sum(Col6) GROUP BY Col3, Col1, Col2, Col4, Col5 ORDER BY Col5 LABEL Col3 'ticket', Col5 'month', Col4 'year', sum(Col6) 'total'"
)
Output:
A B C D
ticket month year total
MME-1 1 2017 3
MME-2 1 2017 2
VAN-1 1 2017 2
MME-3 2 2017 2
XMR-1 2 2017 1
MME-2 3 2017 1