Excel FIlter Function for blank and multiple criteria

[Justplainj]

HI All,

Sorry for the TL:DR

I have a large data set spanning 10+ years of employment data (unfortunately i cannot share the file as it is people personal information).

What I am trying to do is use a drop down list to pick a month and this filters the list.
To do this i use the filter function as follows.
=FILTER(A1:X2450,(V1:V2450 = "")*(V1:V2450 > AJ1)*(U1:U2450 < AJ1),"")

The above function obviously does not work seeing the "=" clashes with the rest of the functions permutations.

What I am trying to achieve is filter the termination date column (column v) to include a date entered in cell AJ1 but also include (and display) all cells that are blank in column V.
The end result will be to include all rows that has no terminated date (meaning active employees), as well as people after (or greater as) the date in AJ1, because if you traverse into the past these people will not have been terminated yet.
The third permutation referencing column U is the hire date column, which would then exclude people after the date in question as these people would not have been hired yet (seeing as you look at the past).

The end result should be a table of the data in column A to X which displays all people excluding those hired after the date in AJ1, include people terminated after the date in AJ1 and all rows where the termination date (column V) is blank).

The reason i want to use this filter function is so that the data can be subjected to other formulas.

I am also open to any other ideas.

Some additional background.
I currently use several pivots (over 120 for the 10 years) with the date filtered to show only a particular month and then additional pivots to track other stats such as male vs female count etc.
This is extremely cumbersome as i need to update the date filters of the pivots, every month new data is received about who left the business or who joined.

I was therefore hoping to just use the filter function to filter the main table then pivot the new table created by the filter function.

Thanks in advance
J

 

[RoryA]

You need to add the criteria for an OR, so something like:

=FILTER(A1:X2450,(V1:V2450 = "")+((V1:V2450 > AJ1)*(U1:U2450 < AJ1)),"")

assuming that you want both of:
V is blank, or
V is >AJ1 and U is < AJ1

 

[Justplainj]

Hi All,

I have partially figured it out by using the or "+" statement for this function.

=FILTER(A2:X2450,(V2:V2450 = "")+(V2:V2450 > AJ1)*(U2:U2450 <= AJ1),"")

However this is still including an additional day in column U.

Example.
Column V = blank (this allows to include all that has no termination date, i.e. active employees)
Column V > AJ1 (if AJ1 is the 30 November 2021, this will include all employees terminated from and including 01 December 2021)
Column U < AJ1 (if AJ1 is the 30 November 2021, this will include all employees with a start date on or before 30 November 2021)

The issue I am still having is, the new table created by the above filter function still includes the people hired on 1 December 2021, skewing the data.

I am not sure why if I state column U must be "<=" 30 November 2021 why it is including the 1 December 2021 in this column.

Thanks
J

 

[Justplainj]

You need to add the criteria for an OR, so something like:

=FILTER(A1:X2450,(V1:V2450 = "")+((V1:V2450 > AJ1))*(U1:U2450 < AJ1)),"")

assuming that you want both of:
V is blank, or
V is >AJ1 and U is < AJ1

Thanks for your reply RoryA

Seems I posted my reply the same time as yours.

 

[RoryA]

It sounds like your logic is actually:

V is blank or V is >AJ1,
and U is < AJ1

in which case:

=FILTER(A1:X2450,((V1:V2450 = "")+(V1:V2450 > AJ1))*(U1:U2450 < AJ1),"")

 

[Justplainj]

It sounds like your logic is actually:

V is blank or V is >AJ1,
and U is < AJ1

in which case:

=FILTER(A1:X2450,((V1:V2450 = "")+(V1:V2450 > AJ1))*(U1:U2450 < AJ1)),"")

Hi RoryA,

Thanks this worked.
I really appreciate.
One correction for those reading this post wanting the solution. There are 2 closing parenthesis before the last Comma. I changed it to one.
=FILTER(A1:X2450,((V1:V2450 = "")+(V1:V2450 > AJ1))*(U1:U2450 < AJ1),"")

 

[RoryA]

There are 2 closing parenthesis before the last Comma

Good point - I've amended my post to reflect that to make it less confusing for anyone finding this answer later!