Number of Staff during each 30 minute interval

[tinferns]

Hello Team.. another request.. Been thru similar requests threads..none matched my requirement.

Based on the below RAW DATA, I want to know how many staff I have at any given time.

SAMPLE RAW DATA:
[TABLE="width: 786"]
<colgroup><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]EMP ID[/TD]
[TD]Emp Name[/TD]
[TD]Supervisor[/TD]
[TD]14-Oct-18[/TD]
[TD]15-Oct-18[/TD]
[TD]16-Oct-18[/TD]
[TD]17-Oct-18[/TD]
[TD]18-Oct-18[/TD]
[TD]19-Oct-18[/TD]
[TD]20-Oct-18[/TD]
[/TR]
[TR]
[TD]1070273[/TD]
[TD]Thomas Moses[/TD]
[TD]Martin Fernandes[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]Vacation[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[/TR]
[TR]
[TD]1071115[/TD]
[TD]Allan Walker[/TD]
[TD]Ricardo Bravo[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[/TR]
[TR]
[TD]1069788[/TD]
[TD]Allison Moyet[/TD]
[TD]Ricardo Bravo[/TD]
[TD]13:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]1073237[/TD]
[TD]Pink Floyd[/TD]
[TD]Martin Fernandes[/TD]
[TD]Vacation[/TD]
[TD]05:30[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[/TR]
[TR]
[TD]1072766[/TD]
[TD]Phill Collins[/TD]
[TD]Anson Palio[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1071247[/TD]
[TD]Sam Richardson[/TD]
[TD]Ricardo Bravo[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1070905[/TD]
[TD]Samantha Fox[/TD]
[TD]Martin Fernandes[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]20:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]20:30[/TD]
[/TR]
[TR]
[TD]1070272[/TD]
[TD]Anifa Sequeira[/TD]
[TD]Anson Palio[/TD]
[TD]OFF[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1067361[/TD]
[TD]Poison Ivy[/TD]
[TD]Ricardo Bravo[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[TD]Vacation[/TD]
[/TR]
[TR]
[TD]1067150[/TD]
[TD]Anthony Stallon[/TD]
[TD]Martin Fernandes[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[TD]OFF[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1073477[/TD]
[TD]Joshua Fernandez[/TD]
[TD]Anson Palio[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]1067068[/TD]
[TD]Jude Patrick[/TD]
[TD]Ricardo Bravo[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[/TR]
[TR]
[TD]1073839[/TD]
[TD]Barry Heldt[/TD]
[TD]Martin Fernandes[/TD]
[TD]18:30[/TD]
[TD]18:30[/TD]
[TD]18:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]18:30[/TD]
[TD]18:30[/TD]
[/TR]
[TR]
[TD]1062698[/TD]
[TD]Ben Isaac[/TD]
[TD]Anson Palio[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]07:30[/TD]
[TD]07:30[/TD]
[/TR]
[TR]
[TD]1072024[/TD]
[TD]Christopher R[/TD]
[TD]Martin Fernandes[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]11:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1064043[/TD]
[TD]Clifford Das[/TD]
[TD]Anson Palio[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[TD]12:30[/TD]
[TD]Vacation[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1073989[/TD]
[TD]Morten Harket[/TD]
[TD]Ricardo Bravo[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]1073533[/TD]
[TD]Delphinia Dkhar[/TD]
[TD]Anson Palio[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]16:30[/TD]
[TD]16:30[/TD]
[/TR]
[TR]
[TD]1067367[/TD]
[TD]Cheryl Rodrigues[/TD]
[TD]Martin Fernandes[/TD]
[TD]09:30[/TD]
[TD]09:30[/TD]
[TD]09:30[/TD]
[TD]09:30[/TD]
[TD]Vacation[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</tbody>[/TABLE]


I have 90 such entries.

Each person works for 9 hours straight.

I don't bother about breaks.

Below are the shifts we operate:

SHIFT TIMINGS:
[TABLE="width: 186"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05:30[/TD]
[TD]14:30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]07:30[/TD]
[TD]16:30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]09:30[/TD]
[TD]18:30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11:30[/TD]
[TD]20:30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12:30[/TD]
[TD]21:30[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]13:30[/TD]
[TD]22:30[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]14:30[/TD]
[TD]23:30[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16:30[/TD]
[TD]01:30[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]17:30[/TD]
[TD]02:30[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]18:30[/TD]
[TD]03:30[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20:30[/TD]
[TD]05:30[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]21:30[/TD]
[TD]06:30[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]22:30[/TD]
[TD]07:30[/TD]
[/TR]
</tbody>[/TABLE]

SUGGESTED OUTPUT: (Could be incorrect.. I did this manually!)
[TABLE="width: 640"]
<colgroup><col width="80" span="8" style="width:60pt"> </colgroup><tbody>[TR]
[TD="width: 80"][TABLE="width: 640"]
<colgroup><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]14-Oct-18[/TD]
[TD]15-Oct-18[/TD]
[TD]16-Oct-18[/TD]
[TD]17-Oct-18[/TD]
[TD]18-Oct-18[/TD]
[TD]19-Oct-18[/TD]
[TD]20-Oct-18[/TD]
[/TR]
[TR]
[TD]05:30[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:30[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07:00[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14:30[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:30[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:30[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18:30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20:30[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:30[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02:30[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03:00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03:30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04:00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04:30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05:00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"][/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a formula like Sumproduct. Please avoid VBA .

Await your reply.

Thanks

martin

 

[Eric W]

I put the new table on another sheet. Try:

Book1
	A	B	C	D	E	F	G	H
1		14-Oct-18	15-Oct-18	16-Oct-18	17-Oct-18	18-Oct-18	19-Oct-18	20-Oct-18
2	5:30	0	1	0	0	0	0	0
3	6:00	0	1	0	0	0	0	0
4	6:30	0	1	0	0	0	0	0
5	7:00	0	1	0	0	0	0	0
6	7:30	2	3	1	1	1	2	1
7	8:00	2	3	1	1	1	2	1
8	8:30	2	3	1	1	1	2	1
9	9:00	2	3	1	1	1	2	1
10	9:30	3	4	2	2	1	2	1
11	10:00	3	4	2	2	1	2	1
12	10:30	3	4	2	2	1	2	1
13	11:00	3	4	2	2	1	2	1
14	11:30	4	6	4	4	3	3	1
15	12:00	4	6	4	4	3	3	1
16	12:30	6	8	5	5	4	4	2
17	13:00	6	8	5	5	4	4	2
18	13:30	8	9	7	8	7	6	4
19	14:00	8	9	7	8	7	6	4
20	14:30	8	8	7	8	7	6	4
21	15:00	8	8	7	8	7	6	4
22	15:30	8	8	7	8	7	6	4
23	16:00	8	8	7	8	7	6	4
24	16:30	8	8	8	8	7	5	4
25	17:00	8	8	8	8	7	5	4
26	17:30	8	8	8	8	7	5	4
27	18:00	8	8	8	8	7	5	4
28	18:30	8	8	8	7	7	6	5
29	19:00	8	8	8	7	7	6	5
30	19:30	8	8	8	7	7	6	5
31	20:00	8	8	8	7	7	6	5
32	20:30	9	8	9	9	8	7	8
33	21:00	9	8	9	9	8	7	8
34	21:30	7	6	8	8	7	6	7
35	22:00	7	6	8	8	7	6	7
36	22:30	5	5	6	5	4	4	5
37	23:00	5	5	6	5	4	4	5
38	23:30	5	5	6	5	4	4	5
39	0:00	5	5	6	5	4	4	5
40	0:30	5	5	6	5	4	4	5
41	1:00	5	5	6	5	4	4	5
42	1:30	3	3	4	4	3	3	4
43	2:00	3	3	4	4	3	3	4
44	2:30	3	3	4	4	3	3	4
45	3:00	3	3	4	4	3	3	4
46	3:30	2	2	3	4	3	2	3
47	4:00	2	2	3	4	3	2	3
48	4:30	2	2	3	4	3	2	3
49	5:00	2	2	3	4	3	2	3
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet1!D$2:D$40,">"&IF($A2<=5/24,$A2+15/24,$A2-9/24),Sheet1!D$2:D$40,"<="&IF($A2<=5/24,$A2+1,$A2))

Copy down and across as needed. Let us know how it works, I got several variances from your example.

 

[tinferns]

Thanks Eric. It seems to be a perfect fit. I believe it is accurate... however will audit the same and get back. Thanks a million.