How to adjust formula to look at multiple dates

[Dena]

I have attached my spreadsheet. Tab "Student Worker Pay" column F has a formula that refers to tab "Student Worker List". When a worker is hired I log in their information in the "Student Worker List" tab with their eligible dates and department. Assuming that there is no break in the dates for the same dept and title, my formula works beautifully. My problem occurs when a worker is hired more than once for the same title and same dept but with a gap in dates.

So example:
Jane Doe works for Biology as a Stu 1 5/10-5/20
Then she resigns or has to leave but is brought back for Biology as a Stu 1 from 6/5-6/30.

My formula automatically creates an "I" for inactive per my formula since it doesn't look at both sets of dates and determines that the dates I enter on "Student Worker Pay" in columns D and E fall within either one of those ranges.

Any suggestions?

The purpose of my formula is to help determine when I receive a timesheet, when I enter in their dates if they were eligible to actually work. I get an A if they are and an I if they are not and require hire paperwork. But since a lot of students do have a break in hire dates, I get an I even when they are eligibile.

I am hoping someone has some insight.

13-14 budget.xlsx

12-13 budget.xlsx
This is my current years spreadsheet that shows more examples.... I want to avoid all of the I's this coming year.

[FDibbins]

Hi

Not sure if this will help or not...
I added a helper column on SWLin G and copied this down...
=IF(A2=A3,E3,E2) =A2&B2&C2
Then changed the reference in SWP to this...
=IF(A2="","",IF(AND(D2>=SUMIFS('Student Worker List'!D:D,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2,'Student Worker List'!C:C,C2),E2<=SUMIFS('Student Worker List'!G:G,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2,'Student Worker List'!C:C,C2)),"A","I"))

[newdoverman]

I have made some changes to your workbook that you might want to consider.

1) I added a column for ID# on the Student List and Pay sheets.

2) I added two columns to the Pay sheet that would show (automatically) the start and termination dates so that you have easy verification of dates.

3) The formulae have drastically changed on my proposed workbook.

The ID# makes it easy to compare the student's actual times of employment IF each term of employment has its own attached ID#. This usage of an ID# is quite common in large enterprises that may have the same employees entering and leaving the workforce. The names stay the same but each term of employment has its own "financial coding" or ID attached to the employee.

[newdoverman]

Here is a further refinement.

[Dena]

HELP,

I don't know what happened but my spreadsheet was working but now it isn't. Can you look at the formula in column C, D, and J on "Student worker pay" tab. C needs to match the dept on "Student worker List" based on the ID#. Everyone is Summer right now and then the title should also match. So Briana her needs to say NSD for dept and Stu 3. Based on that information column J needs to show $10.00. I am only focusing on row 2 right now. So if I can get that row correct, then I will drag the formulas down.

13-14 budget.xlsm