Number of staff in building - date/time issues

[khutchy]

Hi
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.

A sample of the data is as follows;

[TABLE="width: 470"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Staff No:[/TD]
[TD]Arrival time[/TD]
[TD]Processed time[/TD]
[TD]Exit time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]26/02/2013 07:13[/TD]
[TD]26/02/2013 07:22[/TD]
[TD]26/02/2013 13:52[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]26/02/2013 07:25[/TD]
[TD]26/02/2013 07:36[/TD]
[TD]26/02/2013 14:12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]26/02/2013 07:36[/TD]
[TD]26/02/2013 07:54[/TD]
[TD]26/02/2013 16:41[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]26/02/2013 07:41[/TD]
[TD]26/02/2013 08:01[/TD]
[TD]26/02/2013 19:18[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]26/02/2013 08:06[/TD]
[TD]26/02/2013 08:13[/TD]
[TD]26/02/2013 18:45[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]27/02/2013 07:13[/TD]
[TD]27/02/2013 07:22[/TD]
[TD]27/02/2013 21:52[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]27/02/2013 07:25[/TD]
[TD]27/02/2013 08:06[/TD]
[TD]27/02/2013 14:12[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]27/02/2013 07:36[/TD]
[TD]27/02/2013 07:54[/TD]
[TD]27/02/2013 16:41[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]27/02/2013 07:41[/TD]
[TD]27/02/2013 08:01[/TD]
[TD]27/02/2013 19:18[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]28/02/2013 08:06[/TD]
[TD]28/02/2013 08:13[/TD]
[TD]28/02/2013 18:45[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]28/02/2013 07:13[/TD]
[TD]28/02/2013 07:22[/TD]
[TD]28/02/2013 22:52[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]28/02/2013 07:25[/TD]
[TD]28/02/2013 07:36[/TD]
[TD]28/02/2013 14:12[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]28/02/2013 07:36[/TD]
[TD]28/02/2013 07:54[/TD]
[TD]28/02/2013 16:41[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28/02/2013 07:41[/TD]
[TD]28/02/2013 08:01[/TD]
[TD]28/02/2013 20:18[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]28/02/2013 08:06[/TD]
[TD]28/02/2013 08:13[/TD]
[TD]28/02/2013 21:45[/TD]
[/TR]
</tbody>[/TABLE]

I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!

Any help appreciated

 

[MARK858]

Perhaps this will give you some ideas.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1					Clear time	Time of day	People in building
2	Staff No:	Arrival time	Processed time	Exit time			26/02/2013	27/02/2013	28/02/2013	Average (rounded up)
3	1	26/02/2013 07:13	26/02/2013 07:22	26/02/2013 13:52	0:09:00	07:00	0	0	0	0
4	2	26/02/2013 07:25	26/02/2013 07:36	26/02/2013 14:12	0:11:00	08:00	3	2	3	3
5	3	26/02/2013 07:36	26/02/2013 07:54	26/02/2013 16:41	0:18:00	09:00	5	4	6	5
6	4	26/02/2013 07:41	26/02/2013 08:01	26/02/2013 19:18	0:20:00	10:00	5	4	6	5
7	5	26/02/2013 08:06	26/02/2013 08:13	26/02/2013 18:45	0:07:00	11:00	5	4	6	5
8	6	27/02/2013 07:13	27/02/2013 07:22	27/02/2013 21:52	0:09:00	12:00	5	4	6	5
9	7	27/02/2013 07:25	27/02/2013 08:06	27/02/2013 14:12	0:41:00	13:00	5	4	6	5
10	8	27/02/2013 07:36	27/02/2013 07:54	27/02/2013 16:41	0:18:00	14:00	4	4	6	5
11	9	27/02/2013 07:41	27/02/2013 08:01	27/02/2013 19:18	0:20:00	15:00	3	3	5	4
12	10	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 18:45	0:07:00	16:00	3	3	5	4
13	11	28/02/2013 07:13	28/02/2013 07:22	28/02/2013 22:52	0:09:00	17:00	2	2	4	3
14	12	28/02/2013 07:25	28/02/2013 07:36	28/02/2013 14:12	0:11:00	18:00	2	2	4	3
15	13	28/02/2013 07:36	28/02/2013 07:54	28/02/2013 16:41	0:18:00	19:00	1	2	3	2
16	14	28/02/2013 07:41	28/02/2013 08:01	28/02/2013 20:18	0:20:00	20:00	0	1	3	2
17	15	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	21:00	0	1	2	1
18						22:00	0	0	1	1
Sheet1

 

[khutchy]

Mark

This is excellent and nearly sorts all the query.

Is it possible to show the average number of persons in the building (J column) separately for each day of the week (Mon-Fri). Appreciate the sample data set was only for 3 days, but we operate 7 days and I am trying to ascertain trends on each day of the week over a 3 month period

Much appreciated

Keith

 

[MARK858]

Please note that the formula in Column K must be entered with Ctrl-Shift-Enter not just enter (do not put in the curly brackets manually)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1					Clear time	Time of day	People in building
2							Tue	Wed	Tue
3	Staff No:	Arrival time	Processed time	Exit time			26/02/2013	27/02/2013	05/03/2013	Day	Day Av
4	1	26/02/2013 07:13	26/02/2013 07:22	26/02/2013 13:52	0:09:00	07:00	0	0	0	Tue	3
5	2	26/02/2013 07:25	26/02/2013 07:36	26/02/2013 14:12	0:11:00	08:00	3	2	3	Wed	2.5
6	3	26/02/2013 07:36	26/02/2013 07:54	26/02/2013 16:41	0:18:00	09:00	5	4	5
7	4	26/02/2013 07:41	26/02/2013 08:01	26/02/2013 19:18	0:20:00	10:00	5	4	5
8	5	26/02/2013 08:06	26/02/2013 08:13	26/02/2013 18:45	0:07:00	11:00	5	4	5
9	6	27/02/2013 07:13	27/02/2013 07:22	27/02/2013 21:52	0:09:00	12:00	5	4	5
10	7	27/02/2013 07:25	27/02/2013 08:06	27/02/2013 14:12	0:41:00	13:00	5	4	5
11	8	27/02/2013 07:36	27/02/2013 07:54	27/02/2013 16:41	0:18:00	14:00	4	4	5
12	9	27/02/2013 07:41	27/02/2013 08:01	27/02/2013 19:18	0:20:00	15:00	3	3	4
13	10	05/03/2013 00:00	05/03/2013 08:13	05/03/2013 18:45	8:13:00	16:00	3	3	4
14	11	05/03/2013 07:13	05/03/2013 07:22	05/03/2013 22:52	0:09:00	17:00	2	2	3
15	12	05/03/2013 07:25	05/03/2013 07:36	05/03/2013 14:12	0:11:00	18:00	2	2	3
16	13	05/03/2013 07:36	05/03/2013 07:54	05/03/2013 16:41	0:18:00	19:00	1	2	2
17	14	05/03/2013 07:41	05/03/2013 08:01	05/03/2013 20:18	0:20:00	20:00	0	1	2
18	15	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	21:00	0	1	1
19						22:00	0	0	1
Sheet1

 

[khutchy]

Mark

Many thanks thus far. I didnt outline the reply very well. What I was trying to do with the average people in building was show it broken down by day / hour as below


[TABLE="width: 523"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="colspan: 7"]Avg numbers in building[/TD]
[/TR]
[TR]
[TD]Time of day[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]07:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I tried to vary the formula above in K4

K4	{=AVERAGE(IF($G$2:$I$2=J4,$G$4:$I$19))}

<tbody>
</tbody>

But when I vary it to try to obtain the avg numbers on Tues at 0800hrs I am getting the total (6) divided by all the days in the row (x2 Tue and 1 Wed) instead of just the x2 Tues.

K

 

[MARK858]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1					Clear time	Time of day	People in building
2							Tue	Thu	Tue
3	Staff No:	Arrival time	Processed time	Exit time			26/02/2013	28/02/2013	05/03/2013	Time	Tue	Thu
4	1	19/02/2013 07:13	19/02/2013 07:22	19/02/2013 13:52	0:09:00	07:00	0	0	0	07:00	0	0
5	2	19/02/2013 07:25	19/02/2013 07:36	19/02/2013 14:12	0:11:00	08:00	2	0	1	08:00	1.5	0
6	3	19/02/2013 07:36	19/02/2013 07:54	19/02/2013 16:41	0:18:00	09:00	4	4	2	09:00	3	4
7	4	19/02/2013 07:41	19/02/2013 08:01	19/02/2013 19:18	0:20:00	10:00	4	4	2	10:00	3	4
8	5	19/02/2013 08:06	19/02/2013 08:13	19/02/2013 18:45	0:07:00	11:00	4	4	2	11:00	3	4
9	6	26/02/2013 07:13	26/02/2013 07:22	26/02/2013 21:52	0:09:00	12:00	4	4	2	12:00	3	4
10	7	26/02/2013 07:25	26/02/2013 08:06	26/02/2013 14:12	0:41:00	13:00	4	4	2	13:00	3	4
11	8	26/02/2013 07:36	26/02/2013 07:54	26/02/2013 16:41	0:18:00	14:00	4	4	2	14:00	3	4
12	9	26/02/2013 07:41	26/02/2013 08:01	26/02/2013 19:18	0:20:00	15:00	3	4	2	15:00	2.5	4
13	10	05/03/2013 00:00	05/03/2013 08:13	05/03/2013 18:45	8:13:00	16:00	3	4	2
14	11	05/03/2013 07:13	05/03/2013 07:22	05/03/2013 22:52	0:09:00	17:00	2	4	2
15	12	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	18:00	2	4	2
16	13	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	19:00	2	4	1
17	14	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	20:00	1	4	1
18	15	28/02/2013 08:06	28/02/2013 08:13	28/02/2013 21:45	0:07:00	21:00	1	4	1
19						22:00	0	0	1
Sheet3

 

[khutchy]

Mark

Works perfectly. You're a legend. Many thanks

K