filter dates formula using advance criteria.

[wayneseymour]

I have an advanced filter that runs on my Excel VBA application which is suppose to filter dates in the data base, I created the advance filter on the worksheet and it works fine except for when it comes to filtering dates this is the formula I'm using:


=IF(C5="",">1","<=" &C5) start date


=IF(C5="","<60000","<" &C5) end date


I tried removing the Quotes from the <60000 but received a error.
the filter will not filter the dates requested.

For example I ask to filter date between 9/20/2018 and 9/30/2018 I get results outside those dates as well as the dates requested.
another example ,If I ask for dates of say 5/1/2016 and 5/30/2016 I get dates from 4/1/2016 as well. I I ask for all records with the date of 9/29/2018 I get nothing.

 

[arthurbr]

Are the dates real dates or text looking like dates ? (in the latter case they are left aligned in the cells if no custom alignment is applied)

 

[wayneseymour]

No they are date the entire Column is formatted for dates.

 

[arthurbr]

The formatting is not what matters, it's the underlying data
Just to be sure use the TYPE function on one of the dates. If it does not return 1, they are not real dates

 

[wayneseymour]

The formatting is not what matters, it's the underlying data
Just to be sure use the TYPE function on one of the dates. If it does not return 1, they are not real dates

I used the "TYPE" function and all cells returned 1

 

[arthurbr]

I'm sorry, I have no knowledge of VBA, but I don't understand what =IF(C5="",">1","<=" &C5) start date is supposed to do

 

[wayneseymour]

I'm sorry, I have no knowledge of VBA, but I don't understand what =IF(C5="",">1","<=" &C5) start date is supposed to do

In the Advance Criteria filter It will allow you to filter your advance filtered data between dates .
=IF(C5="",">1","<=" &C5) and =IF(B5="",">1","<=" &B5) is your End date and start dates respectfully.
I worked out a small portion of the problem I was having, but the main thing that still bugs me is that the advance filter will not filter the dates if the End date and Start dates are the same,In addition to get a correct scope of the dates you have to enter a day ahead in the start date and a day after in the end date in order to get the dates of data you are filtering for. Example. if the dates you want are say 4/28/2018 you have to enter 4/27/2018 for your start date and 4/29/2018 for your end date. if not and you enter 4/28/2018 as your start and end dates you get no results. If you are filtering for dates at the end of a month your end date has to be the following month ie. you want records at the end of April 4/30/2018, you have to place 4/29/2018 as your start date and 5/1/2018 as your end date., then you will get all records for the 30th. But if you use the 30th as the start and end date you get no results. hope you follow what i'm saying.