| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of employee's working a Shift
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find number of employee's working a Shift
=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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find number of employee's working a Shift
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 "Bob Phillips" wrote: =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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find number of employee's working a Shift
=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) -- Regards, Tom Ogilvy "Sonic" wrote in message ... 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 "Bob Phillips" wrote: =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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find number of employee's working a Shift
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" wrote: =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) -- Regards, Tom Ogilvy "Sonic" wrote in message ... 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 "Bob Phillips" wrote: =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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find number of employee's working a Shift
=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")) -- Regards, Tom Ogilvy "Sonic" wrote in message ... 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" wrote: =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) -- Regards, Tom Ogilvy "Sonic" wrote in message ... 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 "Bob Phillips" wrote: =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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CTRL+SHIFT+) is not working in Excel 2007 | Excel Discussion (Misc queries) | |||
| held the shift key down for too long so now keyboard not working | Excel Discussion (Misc queries) | |||
| SHIFT + CONTROL + DOWN ARROW is not working! | Excel Discussion (Misc queries) | |||
| Shift key to disable macros not working | Excel Discussion (Misc queries) | |||
| Return employee name when the employee's number is entered | Excel Worksheet Functions | |||
Hybrid Mode
