Find number of employee's working a Shift

[Sonic]

OK heres a good one for you guys to help me with

A B C D E F
1Name In Out Total Pay Total
Employee Time Time Time Rate Pay

2 Sean 5:00 am 1:00 pm 8 $7.00 $56.00

Now On Sheet2 I want to pull the number of hours that sean worked between my
shift which are 5 to 11am, 11am to 1:30pm, 1:30 to 5:00pm 5 to 8pm and 8 to
12am. So if it works it would tell me that i had one employee for shift 5 to
11 and one for 11 to 1:30. Simple Right at least i hope so.
Thanks in advance

 

[Bob Phillips]

=SUMPRODUCT(--(B2:B20>=--"05:00:00"),--(B2:B20<=--"11:00:00"))

=SUMPRODUCT(--(B2:B20>=--"11:00:00"),--(B2:B20<=--"13:30:00"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sonic]

Great thats prefect now a seacond part to that would be if i could pull the
total pay that was payed out for my diffrent shifts

 

[Tom Ogilvy]

=SUMPRODUCT(--(B2:B20>=--"05:00:00"),--(C2:C20<=--"11:00:00"),F2:F20)

=SUMPRODUCT(--(B2:B20>=--"11:00:00"),--(C2:C20<=--"13:30:00"),F2:F20)

 

[Sonic]

Ok it works but if i have a employee works two shifts(11:00am to 5:00pm) then
it will not return that they worked the 1:30 to 5:00.
Shift1 is 5:00am to 11:00am
Shift2 is 11:00am to 1:30pm
Shift3 is 1:30pm to 5:00pm
Shift4 is 5:00pm to 8:00pm
Shift5 is 8:00pm to 10:00pm
Shift6 is 10:00pm to 12:00am
These are my diff shift that I run most the time my employees will work two
to three shifts per day and i would like to know (without counting maunaly)
how many employees are working every shift.

 

[Tom Ogilvy]

=SUMPRODUCT(--($B$1:$B$10<--"11:00 AM"),--($C$1:$C$10>--"5:00 AM"))
=SUMPRODUCT(--($B$1:$B$10<--"1:00 PM"),--($C$1:$C$10>--"11:00 AM"))
=SUMPRODUCT(--($B$1:$B$10<--"5:00 PM"),--($C$1:$C$10>--"1:30 PM"))
=SUMPRODUCT(--($B$1:$B$10<--"8:00 PM"),--($C$1:$C$10>--"5:00 PM"))
=SUMPRODUCT(--($B$1:$B$10<--"10:00 PM"),--($C$1:$C$10>--"8:00 PM"))
=SUMPRODUCT(--($B$1:$B$10<--"11:59:59 PM"),--($C$1:$C$10>--"10:00 PM"))