counting staff between times

[cybergremlin]

Hi all.

Im trying to work out how many staff i have working between two times and also based on the area they work in.

Col A has staff names, Col B has the area they work in, C&D have the start stop time

The times the staff are working between are in col C&D and i wish to break them down by time intervals which are in col F&G then based on the area they work in (col B) and have these in col H to M

So if the employee works 08:00-16:00 and is in area 208 they need to be in J6:J37

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Name

Area

Start

Stop

*

Start

Stop

130

150

208

209

501

2

Agent1

209

16:00

17:00

*

07:00

07:15

3

Agent2

209

08:00

18:00

*

07:15

07:30

4

Agent3

208

08:00

16:00

*

07:30

07:45

5

Agent4

208

09:00

16:00

*

07:45

08:00

6

Agent5

208

10:00

14:00

*

08:00

08:15

7

Agent6

150

12:00

14:00

*

08:15

08:30

8

Agent7

150

14:00

14:10

*

08:30

08:45

9

Agent8

150

14:10

16:15

*

08:45

09:00

10

Agent9

150

16:15

16:25

*

09:00

09:15

11

Agent10

150

16:25

18:00

*

09:15

09:30

12

Agent11

130

16:30

20:00

*

09:30

09:45

13

Agent12

208

10:00

14:10

*

09:45

10:00

14

Agent13

209

10:00

14:20

*

10:00

10:15

15

Agent14

501

07:00

09:00

*

10:15

10:30

<tbody>
</tbody>

thanks

 

[navic]

Try ARRAY formula in H2 and copy across

=IFNA(INDEX($A$2:$A$15,MATCH($F2,IF($B$2:$B$15=H$1,$C$2:$C$15),0)),"")

 

[cybergremlin]

this just seems to add the agent name to 1 cell and also doesn't work if the start time doesn't match 1 of the times in column F. for example Agent8

 

[Repush]

what about (in H2):
=SOMPRODUCT(--($B$2:$B$15=H$1),--($C$2:$C$15<=$F2),--($D$2:$D$15>=$G2))

 

[cybergremlin]

what about (in H2):
=SOMPRODUCT(--($B$2:$B$15=H$1),--($C$2:$C$15<=$F2),--($D$2:$D$15>=$G2))

This is almost perfect apart it seems to cut the times short, Agent2 works till 18:00 but this formula only recognizes it till 17:45

 

[Repush]

Start	Stop	130	150	208	209	501
17:00	17:15	1	1	0	1	0
17:15	17:30	1	1	0	1	0
17:30	17:45	1	1	0	1	0
17:45	18:00	1	1	0	1	0
18:00	18:15	1	0	0	0	0
18:15	18:30	1	0	0	0	0

<tbody>
</tbody>

no problem here . .

 

[cybergremlin]

Hi sorry, it must of been something to do with my formatting, I've changed that and its now working correctly.

thanks for your help

 

[Repush]

OK
thx for the feed back