=Lookup(Multiple names in a array) return the number of hours between 2 times

[Nazerith]

I uploaded the workbook and I need to solve for Q2 and all the other empty cells around there.

THE formula goes somthing like this

P2-P7 is the list name we are looking up under that list is the diffrent positions that are listed in the S colunmb starting at S19.

The list names are listed in the data page

so basied of the List name we need to find all the Positions names listed, and returns how many of thier hours fall between 5am-4pm, and how many of thier hours fall between 4pm-1am Basicly calculating how much labor hours are spent for the day shift and how much labor hours are spent for the night shift.

So in this example Lets take "Line" we have

1 Appetizer S29
1 Grill S39
3 Assembler S26, S31, S38
2 Saute S36, S37
1 Pasta S25,

now form there I just need total hours used before 4pm in Q2, and the hours used after 4pm in S2, for each of the above names using the list lookup name as the referance.


Sorry for the short story 5th time I posted....

[e4excel]

Actually, its very difficult to understand your requirement..can you elaborate as to what are you looking out for in which sheet and from where does the infromation need to come and also provide expected result to mae it easier for the person helping you..

[Nazerith]

Its for Sunday

and in the middle of the page is all the information in the data table.

everything in the associated color is the Department

and i need to return the total hours worked for that color between 5am and 4pm and then 4pm to 1am Lunch and dinner shifts total hours for the department.



so for the first example Q2 i need to return

Oscar G R Assembler 8:00 AM 4:45 PM
Claudia H Pasta 9:00 AM 6:15 PM
Romeo H C Saute 1:00 PM 1:00 AM
Eduardo S Assembler 2:00 PM 8:45 PM
Marcelino L CAssembler 3:00 PM 11:45 PM

the formula will extract
for oscar 8am-4pm=8hrs leaving a remainder of .75hrs
claudia 9am-4pm=7hrs leaving a remainder of 2.25 hrs
Romeo 1pm-4pm=3hrs leaving a remainder of 9hrs
Edguardo 2pm-4pm=2hrs leaving a remainder of 6.75hrs
Marcelino 3pm-4pm=1hr leaving a remainder of 7.75 hrs

so with that said the formula would look up P2 witch = "Line" as a list name in that list is Assembler, Pasta, Saute, Grill, Appetizers. and it will only take the hours from 5am-4pm and place it in Q2= 21hrs for lunch

all the other remainder hours will have the formula that goes in S2 withc will be 23.5 hours + everyone who works after 4 pm please see the Data table in the middle of the page....
hope that clears it up.

[Nazerith]

If anyone knows the answer please help

[Nazerith]

can I please get some help Sorry i have posted 7 times for 3 weeks and no help......

[Nazerith]

still no help =(