Excel: How to filter dates that fall in between selected dates for employee availability

Question

I want to get employee aviability based on date range of their holiday.

Start dates and end dates are date ranges of holiday and I want to get list of employees outside those ranges. What I want to do is enter date range, for example 16.7.- 17.7., and get the available employees for those two days(who are NOT on holiday, so not between start date and end date).

I tried using this command for filtering: =FILTER(A2:C7;(B2:B7>=E2)*(C2:C7<=F2);"Not available")

My thinking behind this filtering is that I would get employees who are on holiday so I would somehow negate the command and get employees NOT filtered.

However, I don't get correct results because of the criteria: C2:C7<=F2

EDIT: I fixed date formatting (dropped the point on the date) and now I don't get any results because I do not know how will I get past End date criteria. Results after fixing date format

Answer 1

See below - it's a problem with the way you built the logic of your formula. Subtract the start dates from your start date range, the end dates from your end date range; and then multiply the results together. If the result is negative, it means that your data set overlaps the range that you set, so it's ok.

If you want to find the employees that are available, you need the logic operation to be >0. If you want to find the employees that are not available, you need the logic operation to be <0.

If you're trying to check for the case of some employee being out exactly on that period, it should be <=0 or >=0.

Answer 2

Here's my attempt: =FILTER(A2:C7;((E2<B2:B7)*(F2<B2:B7)+(E2>C2:C7)*(F2>C2:C7));"Not available")

So step one: (E2<B2:B7)*(F2<B2:B7) Check if an employee is available before the start and end date; In orde to get a TRUE both dates must be smaller;

Step two: (E2>C2:C7)*(F2>C2:C7) check if an employee is available after the start and end date; In orde to get a TRUE both dates must be bigger.