Formula to Calculate number of staff scheduled between times

I am working on a formula to calculate the number of staff between 1/2-hour intervals. I have a spreadsheet set up that shows 62 schedules in columns B2:C63, with the start time in column B and the stop time in column C as shown below:

7 8:00 17:00
8 8:30 17:30
9 8:30 17:30
10 8:30 17:30
11 8:30 17:30
12 9:00 18:00
13 9:00 18:00
14 9:00 18:00
15 9:00 18:00
16 9:00 18:00
17 9:30 18:30

I have intervals set up in another sheet as follows starting in column B2.
8:00
8:30
9:00
9:30
10:00
10:30
11:00

I have already tried the following formula: =COUNTIF(Schedules!B2:B63,">="&Sheet2!B2)-COUNTIF(Schedules!C2:C63,">="&B3)

This is returning values, but they are all incorrect and are not counting the number of agents who are scheduled to work each interval. Based on the data included above, the final interval results should be:

Interval Staff
8:00 1
8:30 5
9:00 10
9:30 11
10:00 11
10:30 11
11:00 11

Please help!

Try:

Formula:

Please Login or Register  to view this content.

Works like a charm!!!! You Rock!

Originally Posted by lspen00

Works like a charm!!!! You Rock!

Glad I could help. If that takes care of this thread could you please mark as solved and add reputation should you see fit.

Thanks