Number of employees function

[brianwakin]

I need to create a function that tells me how many employees are on the
schedule at 7AM, 8AM, 9AM, 10AM, 11AM, 12PM, ...up to 8PM. My schedule has
an in time and an out time in separate cells. Example: Employee1 IN: 10AM
OUT: 4PM / Employee 2 IN: 9AM OUT: 5 pm / Employee 3: IN: 3PM OUT: 8PM ...The
number of employees on the clock at 11AM in this example would be 2. I can't
get Excel to do this. Any help would be appreciated.
Brian

 

[Bob Phillips]

A bit difficult without seeing the data, but suppose the start times are in
B2:Bn, end times are in C2:Cn.

To find how many are in at 8:00, use

=SUMPRODUCT(--(B2:B200<=TIME(8,0,0)),--(C2:C200>=TIME(8,0,0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[brianwakin]

The data is in the following format, but the number of employees is much
greater and changes from week to week and their times in and out change as
well:

Admin Assistant IN OUT
Matt 7:00 AM 5:00 PM
Nelly 8:00 AM 7:00 PM
Natalie 9:00 AM 1:00 PM
Merium 2:00 PM 6:00 PM
Martha 3:00 PM 8:00 PM

Thanks for looking at this.

 

[Bob Phillips]

The formula looks right then, jsut make sure that the range is bigger than
you will ever need.

--

HTH

RP
(remove nothere from the email address if mailing direct)