how many employees are working between each hour

[GaryandWendy]

Anyone have any ideas how I can do this calculation. What I need to d
is find out how many employees are working between each hour.
Example: I will use 4 employees to show you what I’m trying t
accomplish.
Empl. #1 Starts work at 8:00 AM and ends work at 4:30 PM
Empl. #2 Starts work at 9:00 AM and ends work at 5:30 PM
Empl. #3 Starts work at 10:00 AM and ends works to 6:30 PM
Empl. #4 Starts work at 12:00 PM and ends works to 8:30 PM

Between the hours of 8:00 AM and 9:00 AM I have 1 employee working.
Between the hours of 9:00 AM and 10:00 AM I have 2 employees working
Between the hours of 10:00 AM and 11:00 AM I have 3 employees working
Between the hours of 11:00 AM and 12:00 PM I have 3 employees working
Between the hours of 12:00 PM and 1:00 PM I have 4 employees working
And so on.
In B4 I have the hour they start= 8:00 AM =Formatted as Time
In C4 I have the hour they end =4:30 PM = Formatted as Time
In D4 I have total hours worked= 8 It’s actually 8.5 hours but I hav
taken out 1/2 hour for lunch. = Formatted as General
Would appreciate any help on this. E-mail me at (e-mail address removed)
Thank You
Garr

 

[BrianB]

Say we have 4 employees B4:B7 start times, C4:C7 finish times.

Using 24 hour clock, for the hour 8.00 to 9.00
=SUMPRODUCT(($B$4:$B$7<=(8/24))*($C$4:$C$7>=(9/24)))
for the hour 9.00 to 10.00
=SUMPRODUCT(($B$4:$B$7<=(9/24))*($C$4:$C$7>=((10/24)))

I have put it this way, because you can use cell references instead o
the hours 8,9,10 here etc. The cells containing ordinary numbers rathe
than times. So, if cell A12 contains one of the numbers, the formula
which can now be copied, is :-
=SUMPRODUCT(($B$4:$B$7<=(A12/24))*($C$4:$C$7>=((A12+1)/24))