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


[TABLE="width: 187"]
<TBODY>[TR]
[TD]Employee[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Ellis, Christopher[/TD]
[TD]4 A[/TD]
[TD]12 P[/TD]
[/TR]
[TR]
[TD]Emond, Meloney[/TD]
[TD]4 A[/TD]
[TD]12 P[/TD]
[/TR]
[TR]
[TD]Frederick, Alexander[/TD]
[TD]4 A[/TD]
[TD]11 A[/TD]
[/TR]
[TR]
[TD]Gregory, Ricky[/TD]
[TD]4 A[/TD]
[TD]12 P[/TD]
[/TR]
[TR]
[TD]Lavoie, Eric[/TD]
[TD]4 A[/TD]
[TD]12 P[/TD]
[/TR]
[TR]
[TD]Poulin, Patricia[/TD]
[TD]5 A[/TD]
[TD]11 A[/TD]
[/TR]
[TR]
[TD]Andrews, Dale[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[/TR]
[TR]
[TD]Begin, Gary[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hour[/TD]
[TD]Agents Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 A[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 A[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11 A[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 P[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 P[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 P[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11 P[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[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]

[TABLE="width: 560"]
<TBODY>[TR]
[TD="colspan: 2"]Monday[/TD]
[TD="colspan: 2"]Tuesday[/TD]
[TD="colspan: 2"]Wednesday[/TD]
[TD="colspan: 2"]Thursday[/TD]
[TD="colspan: 2"]Friday[/TD]
[TD="colspan: 2"]Saturday[/TD]
[TD="colspan: 2"]Sunday[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]9 A[/TD]
[TD]4 P[/TD]
[TD]9 A[/TD]
[TD]4 P[/TD]
[TD]12 P[/TD]
[TD]4 P[/TD]
[TD]9 A[/TD]
[TD]4 P[/TD]
[TD]9 A[/TD]
[TD]2 P[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]9 A[/TD]
[TD]3 P[/TD]
[TD]9 A[/TD]
[TD]3 P[/TD]
[TD]9 A[/TD]
[TD]3 P[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]10 A[/TD]
[TD]3 P[/TD]
[/TR]
[TR]
[TD]11 A[/TD]
[TD]4 P[/TD]
[TD]11 A[/TD]
[TD]3 P[/TD]
[TD]11 A[/TD]
[TD]3 P[/TD]
[TD]11 A[/TD]
[TD]3 P[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]11 P[/TD]
[TD]7 A[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]6 A[/TD]
[TD]2 P[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[TD]6 A[/TD]
[TD]2 P[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</TBODY><COLGROUP><COL span=14></COLGROUP>[/TABLE]

 

[shawnhet]

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