I want to filter my Sheet with one given date in the filter function.
I got in one Column (K) the start date and on (L) the end date.
Now I want to filter without VBA a specific date (User Input) which is between Column K&L.
My first approach was to concat the two columns for a date range and filter it with the "between" filter.
But unfortunately I can’t use the date filter function on this column.
Have somebody maybe a suggestion to solve this without VBA.
The Excel File is really large up to >1000 rows.
Thank you in advance.
I tried to concat the two columns in a date range to use the inbuild filter option of Excel.
>Solution :
I am pretty sure that this isn’t directly possible without VBA.
How I would approach it is to have a separate cell to hold the date you want to filter, and then have a helper column to calculate whether each row should be shown or not. You can then filter by that column instead.
Like this:
=AND($C$2>=A5,$C$2<=B5)
EDIT – Adding a more efficient formula
The problem with AND is that both conditions are checked, even if the first one fails. This shouldn’t usually be an issue, but if dealing with thousands of formulas, or the conditions are particularly taxing to calculate, it can slow things down.
If needed, we can use IF instead, which short circuits and will only calculate the second condition, if the first is true.
=IF($C$2>=A5,$C$2<=B5)