Sorting groups based on time window

[Rythwin]

Hi,

I'm not sure if this is the right place to post this, but here goes.

I am trying to make a data file with the following requirements / goals:

I have multiple teams with 10-15 members in each team who work in different shifts. For example:

Team A - Log in at 8 AM and Log out at 5 PM
Team B - Log in at 10 AM and Logout at 7 PM
Team C - Log in at 1 PM and Logout at 10 PM

Now I want to create a workbook in a manner where If I input a time slot (example 5 PM to 7 PM) - I get a list of the team members who are not working during that time (In this case I would get a list of team A's names).

I'm fairly new to excel and it's features - so I'm having trouble coming up with the logic process I'll have to follow to create this kind of a file. Any inputs would be greatly appreciated!

Thanks,
Rythwin

[GeoffW283]

Here is a possible starting point for you.

rythwin.png

On the "Employees" worksheet I have time in col-A at one hour intervals.
Row-1 is a list or employees
Row-2 is the team to which the employee is allocated.
There is one column per employee showing what hours that employee works
Col-H gives you total staff in any given hour.

The "Teams" worksheet is used by the "Employees" worksheet to look up the shift start and end for each team.

There is just one formula in Employees!B3 copied across and down to G19.
=IF(AND(INDEX(Teams!$B$2:$B$4, MATCH(B$2, Teams!$A$2:$A$4, 0))<=$A3, INDEX(Teams!$C$2:$C$4, MATCH(B$2, Teams!$A$2:$A$4, 0))>$A3), 1, "")

I hope this gives you some ideas for how your design might work.

[Rythwin]

That is a helpful start, but I don't want to track their logged hours.. I want to be able to pull the names of the employees who are not logged in at a give time window of 1 hour or so.

Thanks for the start though, it's useful and it has got my brain moving somewhere

[GeoffW283]

If you need further specific advice then please attach a sample workbook. To do this please follow the instructions in the yellow banner at the top of this thread that is titled "HOW TO ATTACH YOUR SAMPLE WORKBOOK".