Advanced Rota Formula-Counting Hours

[Danman100]

Hi,

I am creating a rota on Excel for work and I am trying to figure out how to set a formula that calculates how many staff are working at each our of the day. The rota displays each colleagues start and finish time but I can not figure out how to count staff hourly ie. how many staff in store from 06:00-07:00, how many staff in store from 07:00-08:00 etc.
Probably wasting my time trying to create this formula as I don't think that one exists.
Can anybody help please

Dan

 

[gaz_chops]

Welcome to the forum.

Post a sample of your data, see my signature how to.

 

[Danman100]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Week Commencing-		06-Sep
3
4		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
5		06-Sep		07-Sep		08-Sep		09-Sep		10-Sep		11-Sep		12-Sep
6	Rokiah									18:00		16:00
7	Holly
8	Dan	06:30	03:15
9	Nick
10	Lewis
11	Eden	07:30
12	Leigh
13	Jake
14	Phil
15	Janet
16	Kate
17	Louina
18	Lianne
19
20	6:00-7:00	2
21	7:00-8:00	1
22	8:00-9:00	0
23	9:00-10:00	0
24	10:00-11:00	0
25	11:00-12:00	0
26	12:00-13:00	0
27	13:00-14:00	0
28	14:00-15:00	0
29	15:00-16:00	0
30	16:00-17:00	0
31	17:00-18:00	0
32	18:00-19:00	0
33	19:00-20:00	0
34	20:00-21:00	0
35	21:00-22:00	0
Sheet1

Cell Formulas
Range	Formula
B5	=C2
B20	=COUNTIF($B$6:$B$18,">06:00")
B21	=COUNTIF($B$6:$B$18,">07:00")
B22	=COUNTIF($B$6:$B$18,">08:00")
B23	=COUNTIF($B$6:$B$18,">09:00")
B24	=COUNTIF($B$6:$B$18,">10:00")
B25	=COUNTIF($B$6:$B$18,">11:00")
B26	=COUNTIF($B$6:$B$18,">12:00")
B27	=COUNTIF($B$6:$B$18,">13:00")
B28	=COUNTIF($B$6:$B$18,">14:00")
B29	=COUNTIF($B$6:$B$18,">15:00")
B30	=COUNTIF($B$6:$B$18,">16:00")
B31	=COUNTIF($B$6:$B$18,">17:00")
B32	=COUNTIF($B$6:$B$18,">18:00")
B33	=COUNTIF($B$6:$B$18,">19:00")
B34	=COUNTIF($B$6:$B$18,">20:00")
B35	=COUNTIF($B$6:$B$18,">21:00")
D5	=B5+1
F5	=D5+1
H5	=F5+1
J5	=H5+1
L5	=J5+1
N5	=L5+1

 

[Danman100]

Anyone have any ideas yet?

 

[gaz_chops]

Try this,

Split the times in A20:A35 into 2 columns.

In C20 enter

=COUNTIFS(B$6:B$18,"<="&$B20,C$6:C$18,">"&$A20)

Copy down then across to each day under the end times.

[TABLE="width: 325"]
<tbody>[TR]
[TD="colspan: 2"]Week Commencing-[/TD]
[TD="align: right"]06-Sep[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]06-Sep[/TD]
[TD][/TD]
[TD="align: right"]07-Sep[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rokiah[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]13:30[/TD]
[/TR]
[TR]
[TD]Holly[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD="align: right"]06:30[/TD]
[TD="align: right"]18:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07:45[/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD]Eden[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]12:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Leigh[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Janet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Louina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lianne[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06:00[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07:00[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]08:00[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]09:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]11:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]13:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]17:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]