Calculating half hourly coverage

[JamesBowdidge]

Hi,

Cant for th life me wr this one out..

I need to populate this table...

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4		08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00
5	12/11/2018	0.0	0.0	4.0	4.0	4.0	4.0	4.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	5.0	4.0	3.0	4.0	4.0	3.0	3.0	0.0
6	13/11/2018
7	14/11/2018
8	15/11/2018
9	16/11/2018
10	17/11/2018
11	18/11/2018
12	19/11/2018
13	20/11/2018
14	21/11/2018
15	22/11/2018
16	23/11/2018
17	24/11/2018
18	25/11/2018
19	26/11/2018
20	27/11/2018
21	28/11/2018
22	29/11/2018
23	30/11/2018
24	01/12/2018
25	02/12/2018
26	03/12/2018
27	04/12/2018
28	05/12/2018
29	06/12/2018
30	07/12/2018
31	08/12/2018
32	09/12/2018
Coverage

but each cell needs to look up and sum each half hour where someone is onshift from this rota...

Book1
	E	F	G	H	I	J	K	L	M
3	Week1
4	Week 1 - Pattern	Weekly Hrs	12/11/2018	13/11/2018	14/11/2018	15/11/2018	16/11/2018	17/11/2018	18/11/2018
5			Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
6	TLPattern1	37.5	1130 - 2000	1130 - 2000	1130 - 2000	1130 - 2000	0900 - 1700	RDO	RDO
7	TLPattern2	37.5	0900 - 1700	0900 - 1700	0900 - 1700	0900 - 1700	RDO	0900 - 1700	RDO
8	Pattern 1	37.5	0900 - 1730	0900 - 1730	0900 - 1730	0900 - 1730	0900 - 1700	RDO	RDO
9	Pattern 2	15	RDO	1730 - 2000	1730 - 2000	1730 - 2000	RDO	0900 - 1700	RDO
10	Pattern 3	43	0900 - 1900	0900 - 1400	0900 - 1400	0900 - 1800	0900 - 1700	0900 - 1700	RDO
11	Pattern 4	16	1800 - 2000	1400 - 2000	1400 - 2000	1800 - 2000	RDO	RDO	RDO
12	Pattern 5	31.5	1400 - 2000	1400 - 2000	1400 - 2000	1400 - 2000	RDO	0900 - 1700	RDO
13	Pattern 6	27.5	0900 - 1400	0900 - 1400	0900 - 1400	0900 - 1400	0900 - 1700	RDO	RDO
Rota

they are on 2 separate tabs

first time using the HTML screenshot generator so sorry if it hsnt worked!

thanks

 

[JamesBowdidge]

I have manually filled in the 12th for reference..

thanks

J

 

[JamesBowdidge]

Forgot to say,

also have this table..

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
3	Shift	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00
4	0800 - 1230	1	1	1	1	1	1	1	1	1
5	0800 - 1330	1	1	1	1	1	1	1	1	1	1	1
6	0800 - 2000	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7	0830 - 1600		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
8	0900 - 1100			1	1	1	1
9	0900 - 1400			1	1	1	1	1	1	1	1	1	1
10	0900 - 1700			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
11	0900 - 1600			1	1	1	1	1	1	1	1	1	1	1	1	1	1
12	0900 - 1730			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
13	0900 - 1800			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
14	0900 - 1900			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
15	0900 - 2000			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
16	0930 - 1530				1	1	1	1	1	1	1	1	1	1	1	1
17	0930 - 1800				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
18	1030 - 1830						1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
19	1100 - 2000							1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
20	1130 - 2000								1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
21	1200 - 2000									1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
22	1300 - 2000											1	1	1	1	1	1	1	1	1	1	1	1	1	1
23	1400 - 2000													1	1	1	1	1	1	1	1	1	1	1	1
24	1530 - 2000																1	1	1	1	1	1	1	1	1
25	1730 - 2000																				1	1	1	1	1
26	1800 - 2000																					1	1	1	1
27	RDO
Shift Grid

To help lookup and sum each half hour, just cant work out how?

thnks