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

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" wrote in message
...
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

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)


"brianwakin" wrote in message
...
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.
--
Brian


"Bob Phillips" wrote:

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" wrote in message
...
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