how to divide Continuous data into time based groups ?

[paradisein]

Hello Professional,

i have continuous data (time & Kg) of material consumption for 3 working shift of a Factory
i would like to separate this continuous data into 3 Shifts for every day:

for 9.9.2017:
Day shift: from 06:00 till 14:00 average consumption of shift 1=....
Late shift: from 14:00 till 22:00 average consumption of shift 2=....
night shift: from 22:00 till 6:00 average consumption of shift 3=....

then calculate the average consumption per shift for every day

then build a table /chart between the 3 shifts & average consumption to know

which shift has the higher consumption.

below is a sample table of continuous data, i will make this study on 1 year data.
i hope it is not complicated.....and thank you in advance
Best Regards

[TABLE="width: 188"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Kg
[/TD]
[/TR]
[TR]
[TD="align: right"]08.09.2017 22:00
[/TD]
[TD="align: right"]1,93
[/TD]
[/TR]
[TR]
[TD="align: right"]08.09.2017 23:00
[/TD]
[TD="align: right"]2,07[/TD]
[/TR]
[TR]
[TD="align: right"]08.09.2017 23:30[/TD]
[TD="align: right"]1,99[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 00:00[/TD]
[TD="align: right"]1,80[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 01:00[/TD]
[TD="align: right"]2,00[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 02:00[/TD]
[TD="align: right"]1,77[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 04:00[/TD]
[TD="align: right"]2,00[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 05:00[/TD]
[TD="align: right"]2,02[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 06:00[/TD]
[TD="align: right"]2,03[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 07:00[/TD]
[TD="align: right"]2,20[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 08:00[/TD]
[TD="align: right"]2,11[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 09:00[/TD]
[TD="align: right"]2,39[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 10:00[/TD]
[TD="align: right"]2,46[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 11:00[/TD]
[TD="align: right"]2,08[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 12:00[/TD]
[TD="align: right"]2,02[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 13:00[/TD]
[TD="align: right"]2,11[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 15:00[/TD]
[TD="align: right"]2,13[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 16:00[/TD]
[TD="align: right"]1,99[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 17:00[/TD]
[TD="align: right"]1,85[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 18:00[/TD]
[TD="align: right"]2,08[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 20:00[/TD]
[TD="align: right"]1,99[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 21:00[/TD]
[TD="align: right"]1,97[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 22:00[/TD]
[TD="align: right"]1,95[/TD]
[/TR]
[TR]
[TD="align: right"]09.09.2017 23:00[/TD]
[TD="align: right"]1,85[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 00:00[/TD]
[TD="align: right"]1,93[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 01:00[/TD]
[TD="align: right"]2,07[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 02:00[/TD]
[TD="align: right"]2,07[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 03:00[/TD]
[TD="align: right"]1,88[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 05:00[/TD]
[TD="align: right"]2,13[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 06:00[/TD]
[TD="align: right"]1,82[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 07:00[/TD]
[TD="align: right"]2,08[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 08:00[/TD]
[TD="align: right"]2,04[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 10:00[/TD]
[TD="align: right"]2,01[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 11:00[/TD]
[TD="align: right"]2,09[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 12:00[/TD]
[TD="align: right"]2,06[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 13:00[/TD]
[TD="align: right"]2,02[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 14:00[/TD]
[TD="align: right"]2,00[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 15:00[/TD]
[TD="align: right"]2,09[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 16:00[/TD]
[TD="align: right"]1,94[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 17:00[/TD]
[TD="align: right"]1,94[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 18:00[/TD]
[TD="align: right"]1,87[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 19:00[/TD]
[TD="align: right"]1,85[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 20:00[/TD]
[TD="align: right"]1,90[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 21:00[/TD]
[TD="align: right"]2,03[/TD]
[/TR]
[TR]
[TD="align: right"]10.09.2017 22:00[/TD]
[TD="align: right"]1,95[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Szczesny]

Does your time consist of 1 or 2 columns?

 

[paradisein]

Hi,
it is one column
i split it into 2 columns one for date and one for time, but still do not know how to group them
thx

 

[AlanY]

as your dates format is nonstandard I'd replaced all the "." with "/" so that excel can work with, also the "," with "." in the Kg column (note that you probably don't have to do that pending on your excel local setting)

Excel 2013/2016
	A	B	C	D	E
1	Time	Kg		Shift	Average
2	08/09/2017 22:00	1.93	3	1	1.97
3	08/09/2017 23:00	2.07	1	2	2.10
4	08/09/2017 23:30	1.99	1	3	1.97
5	09/09/2017 00:00	1.8	1
6	09/09/2017 01:00	2	1
7	09/09/2017 02:00	1.77	1
8	09/09/2017 04:00	2	1
9	09/09/2017 05:00	2.02	1
10	09/09/2017 06:00	2.03	2
11	09/09/2017 07:00	2.2	2
12	09/09/2017 08:00	2.11	2
13	09/09/2017 09:00	2.39	2
14	09/09/2017 10:00	2.46	2
15	09/09/2017 11:00	2.08	2
16	09/09/2017 12:00	2.02	2
17	09/09/2017 13:00	2.11	2
18	09/09/2017 15:00	2.13	3
19	09/09/2017 16:00	1.99	3
20	09/09/2017 17:00	1.85	3
21	09/09/2017 18:00	2.08	3
22	09/09/2017 20:00	1.99	3
23	09/09/2017 21:00	1.97	3
24	09/09/2017 22:00	1.95	3
25	09/09/2017 23:00	1.85	1
26	10/09/2017 00:00	1.93	1
27	10/09/2017 01:00	2.07	1
28	10/09/2017 02:00	2.07	1
29	10/09/2017 03:00	1.88	1
30	10/09/2017 05:00	2.13	1
31	10/09/2017 06:00	1.82	2
32	10/09/2017 07:00	2.08	2
33	10/09/2017 08:00	2.04	2
34	10/09/2017 10:00	2.01	2
35	10/09/2017 11:00	2.09	2
36	10/09/2017 12:00	2.06	2
37	10/09/2017 13:00	2.02	2
38	10/09/2017 14:00	2	3
39	10/09/2017 15:00	2.09	3
40	10/09/2017 16:00	1.94	3
41	10/09/2017 17:00	1.94	3
42	10/09/2017 18:00	1.87	3
43	10/09/2017 19:00	1.85	3
44	10/09/2017 20:00	1.9	3
45	10/09/2017 21:00	2.03	3
46	10/09/2017 22:00	1.95	3
Sheet1

Cell Formulas
Range	Formula
C2	=IF(OR(A2-INT(A2)<6/24,A2-INT(A2)>22/24),1,IF(AND(A2-INT(A2)>=6/24,A2-INT(A2)<=14/24),2,3))
E2	=AVERAGEIF($C:$C,D2,$B:$B)

 

[paradisein]

Thank you AlanY for the greet Help
it is working like magic ..... unbelievable

i imported large data and ran the code, then come new issue

during the shutdown the Kg is 0.0, so i got a lot of Zero record


some times the shutdown is 1.5 shift for example:

shift 1: 0, 0, 0, 0 av= 0+0+0+0/4 =0
shift 2: 0, 0, 2, 2 av= 0+0+2+2/4 = 1
shift 3: 1, 1, 1.5, 1.5 av= 1.5+1+1.5+2 = 1.5

the shutdown will be overlooked and give false evaluation to the shifts

how can i ignore the zero records ?

your help will be highly appreciated
thx
Paradisein

 

[AlanY]

try amend formula in E2 to

=AVERAGEIFS(B:B,C:C,D2,A:A,">0")

and copy down