Create a Plan of Duties by time

[Pauljj]

I want to be able to see at a glance the number of people I have in at a specific time on a certain duty. I give the example of how it should appear

Book1
	A	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
4		Duty1	Time	to		Duty2	Time	to		Time	07:00	07:30	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
5	Employee1	Alpha	07:00	13:00		Confs	13:00	18:00
6	Employee2									Alpha	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2
7	Employee3									ClientAdv
8	Employee4									Confs													1	1	1	1
9	Employee5									Phone
10	Employee6									PhoneUrg
11	Employee7									Problem
12	Employee8									Quotes
13	Employee9	Alpha	13:00	18:00						RepFee
14	Employee10									Urgent
15	Employee11
16	Employee12
17	Employee13
18	Employee14					Alpha	10:00	21:00
Sheet2

The user would enter the duty and the time the employee would be doing the duty, I simply need the number of people who are doing the duty to appear in the table from column J onwards, any ideas, my problem are between counting the number of times the duty happens and working out the from and to times ?

 

[Pauljj]

Any help on this one please ?

 

[gsbelbin]

Try placing this formula in K6 and then copying to the other cells.

=SUMPRODUCT(--($B$5:$B$18=$J6),--($C$5:$C$18<=K$4),--($D$5:$D$18>=K$4))+SUMPRODUCT(--($F$5:$F$18=$J6),--($G$5:$G$18<=K$4),--($H$5:$H$18>=K$4))<?XML:NAMESPACE PREFIX = K$4),--($D$5 /><K$4),--($D$5:$D$18><?XML:NAMESPACE PREFIX = K$4),--($H$5 /><K$4),--($H$5:$H$18>

This shows 0's where you have blanks. If you want to have blanks then you'll need to adjust the formatting of the cell, I think.

Cheers

Gordon
</K$4),--($H$5:$H$18></K$4),--($D$5:$D$18>

 

[gsbelbin]

Just to clarify something about this. I've used <= and >= in my formula to compare times and this gives slightly different results to what's in your sample data. It depends on how you want to indicate whether people are available at a particular time. Strictly speaking, at 13:00 you've got 3 Alphas there although one is just finishing. If you want to make so that those that are finishing are not available then use this formula.

=SUMPRODUCT(--($B$5:$B$18=$J6),--($C$5:$C$18<=K$4),--($D$5:$D$18>K$4))+SUMPRODUCT(--($F$5:$F$18=$J6),--($G$5:$G$18<=K$4),--($H$5:$H$18>K$4))<K$4),--($D$5:$D$18><K$4),--($H$5:$H$18>

</K$4),--($H$5:$H$18></K$4),--($D$5:$D$18>

 

[Pauljj]

that works a treat, thank you very much