Count Agents Scheduled by half hour

[polarnavs]

I have agents with varying start and end times and I'm trying find a macro or formula to return the number of agents scheduled for the times listed.
I have included a sample set of data and the values that I know the formula or macro should return.
Any help would make a HUGE difference to me.
Thanks

Employee	Start	End
Ellis, Christopher	4 A	12 P
Emond, Meloney	4 A	12 P
Frederick, Alexander	4 A	11 A
Gregory, Ricky	4 A	12 P
Lavoie, Eric	4 A	12 P
Poulin, Patricia	5 A	11 A
Andrews, Dale	6 A	2 P
Begin, Gary	6 A	2 P
Hour	Agents Count
12 A	0
1 A	0
2 A	0
3 A	0
4 A	5
5 A	6
6 A	8
7 A	8
8 A	8
9 A	8
10 A	8
11 A	8
12 P	6
1 P	6
2 P	6
3 P	0
4 P	0
5 P	0
6 P	0
7 P	0
8 P	0
9 P	0
10 P	0
11 P	0

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[shawnhet]

=COUNTIFS($B$2:$B$9,"<="&A10,$C$2:$C$9,">="&A10)

Should work assuming you adjust your ranges accordingly (per the above your source data is in A2:A9 and the beginning of your table is at A10). You can copy it down your whole range.

Cheers,

 

[polarnavs]

This is working great but won't wrap the agents into the next day if their shift overlaps (i.e. 10p-3a). Any thoughts?

 

[shawnhet]

There's probably a better way to do this but this should work.

Next to your end time column (in column D) you can add a helper column with the following expression copied down the range.

=IF(B2>C2,C2+1,C2)


Then use the following expression to count the staff people by 1/2 hour

=COUNTIFS($B$2:$B$9,"<="&A10,$D$2:$D$9,">="&A10)+COUNTIFS($B$2:$B$9,"<="&A10+1,$D$2:$D$9,">="&A10+1)

Cheers,

 

[shawnhet]

Oops, the above doesn't work out. Try:

=COUNTIFS($B$2:$B$9,"<="&A10,$D$2:$D$9,">="&A10)+COUNTIFS($D$2:$D$9,">="&A10+1)

Cheers,

 

[polarnavs]

Some of the times are text (i.e. OFF or PTO).

 

[shawnhet]

I'm not sure how the text values will affect your counts so can you show me some sample data with those text values?

 

[polarnavs]

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Start

End

Start

End

Start

End

Start

End

Start

End

Start

End

Start

End

OFF

OFF

OFF

OFF

OFF

OFF

11 P

7 A

11 P

7 A

11 P

7 A

OFF

OFF

OFF

OFF

9 A

4 P

9 A

4 P

12 P

4 P

9 A

4 P

9 A

2 P

OFF

OFF

11 P

7 A

11 P

7 A

OFF

OFF

OFF

OFF

11 P

7 A

11 P

7 A

OFF

OFF

9 A

3 P

9 A

3 P

9 A

3 P

OFF

OFF

OFF

OFF

OFF

OFF

10 A

3 P

11 A

4 P

11 A

3 P

11 A

3 P

11 A

3 P

OFF

OFF

OFF

OFF

OFF

OFF

OFF

OFF

11 P

7 A

11 P

7 A

11 P

7 A

11 P

7 A

11 P

7 A

OFF

OFF

6 A

2 P

6 A

2 P

6 A

2 P

6 A

2 P

6 A

2 P

OFF

OFF

OFF

OFF

<TBODY>
</TBODY><COLGROUP><COL span=14></COLGROUP>

 

[shawnhet]

I don't believe that text values above should affect the counts. Are you sure it does? If so, how exactly?