Advanced Filter Between Dates
With Excel there are many ways to do the same things and this is another way to filter data between dates. An example using the Excel Autofilter can be found in the following article Autofilter Between Dates This time I will be using Excel's Advanced Filter to isolate dates however we will be shifting the data from a table in one page onto another page. In this way we will be making a summary of the data between two dates with the assistance of a little Excel VBA.
Advanced Filter Between Dates
Being able to filter on more than 2 criteria including dates is important lesson to learn. It further avoids iterations when you need to test conditions involving dates. An article on how to set up an advanced filter manually can be found here. This gives a grounding in how the procedure works in from scratch. The following an example is of a small dataset.
The above dataset is filtered between the Start Date and End Date at the right of screen in BLUE. This is the VBA code to achieve the task.
As you can see the VBA to move the information is quite sleek. I set up a named range called “DataBase” which covers the sheet which contains the data. There is also two formula in Cells C8 and D8. The Excel formula in C8 looks like the following;
=TEXT(Data!$C2,"mmdd")>=TEXT($A$8,"mmdd")
This covers off the From Date and the To Date is as follows.
=TEXT(Data!$C2,"mmdd")<=TEXT($B$8,"mmdd")
These return True or False depending on the date in Column C of the Data sheet. Only the items which produce a True (Greater Than or Equal to the Start Date) and a True (Less Than or Equal to the End Date).
The following Excel file shows a working example using the Advanced Filter with VBA.
