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:

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

<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>

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:

Sr. No.	Start Time	End Time
1	05:30	14:30
2	07:30	16:30
3	09:30	18:30
4	11:30	20:30
5	12:30	21:30
6	13:30	22:30
7	14:30	23:30
8	16:30	01:30
9	17:30	02:30
10	18:30	03:30
11	20:30	05:30
12	21:30	06:30
13	22:30	07:30

<colgroup><col><col><col></colgroup><tbody>
</tbody>

SUGGESTED OUTPUT: (Could be incorrect.. I did this manually!)

14-Oct-18 15-Oct-18 16-Oct-18 17-Oct-18 18-Oct-18 19-Oct-18 20-Oct-18 05:30 0 06:00 0 06:30 0 07:00 0 07:30 2 08:00 2 08:30 2 09:00 2 09:30 2 10:00 2 10:30 2 11:00 2 11:30 3 12:00 3 12:30 3 13:00 3 13:30 5 14:00 5 14:30 5 15:00 5 15:30 5 16:00 5 16:30 5 17:00 3 17:30 3 18:00 3 18:30 3 19:00 3 19:30 3 20:00 3 20:30 4 21:00 4 21:30 4 22:00 4 22:30 4 23:00 2 23:30 2 00:00 2 00:30 2 01:00 2 01:30 2 02:00 2 02:30 2 03:00 2 03:30 1 04:00 1 04:30 1 05:00 1 <colgroup><col><col><col><col span="5"></colgroup><tbody> </tbody>

<colgroup><col width="80" span="8" style="width:60pt"> </colgroup><tbody>
</tbody>

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.