I am trying to find a way to see which employees are not working between 2 dates.
Should I use vlookup, index & match, filter, query, or something else?
- Sheet 1 contains employee details & start/end dates.
- Sheet 2 accepts user input to select 2 dates, and it will automatically display a list of available employees who are not working.
Sheet 1 - Database/Log of all employees and days worked.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | ID | Name | Start Date | End Date |
| 2 | 12345 | John | 01/01/2021 | 01/08/2021 |
| 3 | 54321 | Sarah | 01/24/2021 | 01/29/2021 |
| 4 | 00731 | James | 02/05/2021 | 02/15/2021 |
| 5 | 00731 | John | 02/10/2021 | 02/30/2021 |
Sheet 2 (Row 1-2)- Manually enter in two dates.
| # | A | B |
|---|---|---|
| 1 | Start Date (Manual input) | End Date (Manual input) |
| 2 | 01/01/2021 | 01/30/2021 |
Sheet 2 (Row 3 )- List of all employees that are not working between the two dates entered in Sheet 2!A2:B2 (Expected Results)
| # | A | B |
|---|---|---|
| 3 | ID | Name |
| 4 | 00731 | James |
CodePudding user response：
try:
=INDEX(SUBSTITUTE(UNIQUE(QUERY(""&SPLIT(FLATTEN(IF(SEQUENCE(1, MAX(D2:D-C2:C))<=D2:D-C2:C,
"♥"&A2:A&"♦"&B2:B&"♦"&C2:C SEQUENCE(1, MAX(D2:D-C2:C), 0), )), "♦"),
"select Col1,Col2 where not Col3 matches '"&JOIN("|", "^$",
IF(SEQUENCE(1, G2-F2)<=G2-F2, F2 SEQUENCE(1, G2-F2, 0), ))&"'", 0)), "♥", ))
demo sheet
CodePudding user response：
Assuming the name of your first sheet with the full data is actually Sheet1, place the following in Sheet2 cell A4:
=FILTER(Sheet1!A2:B,Sheet1!A2:A<>"",(Sheet1!C2:C>G2) (Sheet1!D2:D<F2))
The combined either/or condition (Sheet1!C2:C>G2) (Sheet1!D2:D<F2) in the FILTER means "either the start date is after the range date given, or the end date is before the range date given."
The other condition of Sheet1!A2:A<>"" just rules out blank rows in the original data set. It's not strictly necessary as far as the visual results are concerned; but it keeps null rows from being added to those results, which would allow you to enter data below the results in Col A and B of Sheet2 if you wanted, or to have fewer rows in Sheet2 than in Sheet1 without the formula adding more rows to accommodate null returns.
Your posted sample data is unclear. You have two different names for the same ID (i.e., 00731). And you have an "End Date" in D5 of February 30, 2021—which is not a valid date. In any case, it's unclear whether the same person/ID may turn up twice in the original data set. My formula above assumes you will not.
If your original data list may, in fact, contain duplicates, things get a bit trickier. In that case, use the following formula instead, in Sheet2 cell A4:
=UNIQUE(FILTER(A2:B,ISERROR(VLOOKUP(A2:A,FILTER(A2:A,((C2:C>=F2)*(C2:C<=G2)) ((D2:D>=F2)*(D2:D<=G2))),1,FALSE))))
Here, the inner FILTER first forms a list of all people who are working during that range, and then the outer FILTER filters in a UNIQUE set of the people who are not on that inner list (i.e., trying to VLOOKUP them returns IS(an)ERROR).