In this article, you will find some easy methods to filter dates by month and year in Excel. It is very common to filter dates by month or year as you may want to know the total sales of an individual month or year or range of months and years. You can use the Pivot Table, Excel Filter option, or FILTER Function, or can create a VBA Macro button to filter dates by month and year in Excel. In this article, I will describe all the methods with easy steps and clear illustrations.
How to Filter Dates by Month and Year in Excel: 4 Easy Methods
Suppose you have a big dataset where you have sales data with dates. Now you want to filter dates by month and year to get an idea about the sales for a specific month or year. So, we have a dataset on sales quantity and revenue of a product on each date and I will show you how to filter dates by month and year in 4 methods.
1. Use Filter Command
Excel has a Filter Option to filter columns in a table inherently. This is the easiest option to try. Here I am showing the steps.
Steps:
- First, select the column where you want to apply the filter.
- Then, go to the top ribbon and select the Sort & Filter Tab.
- Then, click on the Filter option.
- As a result, you will see a drop-down arrow created in the Header cell of the column.
- Click on this to apply the filter as you want.
- After clicking there will open a window, where will see the list of years and months.
- Now, you can mark or unmark the options to apply a filter on the dates.
- In addition, by clicking on the Plus(+) icon on the left side of the years, you will open the list of months. And again you can click on the plus(+) icon on the left side of any month to open the day’s list.
- Now, mark the specific month and year to filter only for that month and year and unmark all other boxes.
- Now, If mark the year 2022, and the months of May and June from the filter list, you will get the filtered table as shown.
2. Apply Excel FILTER Function
Alternatively, you can also use the Excel Filter Function to filter dates by months and years in Excel. I am showing the steps here.
Steps:
- First, make a table “Filter Criteria” to take input of month and year.
- Then make a table Filtered Output where you will get the output on the filter conditions
- Now insert this formula into the cell F10 to use the FILTER function to filter dates by month and years.
=FILTER(B5:D12,(MONTH(B5:B12)=G5)*(YEAR(B5:B12)=G6),"N/A")
- Enter the value in the month and year cell.
- So, you will get the filtered output for the specific month and year.
- Now, you have to just input the value of month and year in the specific cells to filter dates by month and year using the Filter Function.
3. Utilize Excel PIVOT Table Tool
You can also use the Pivot Table feature to apply a filter on the dates by month and year. I am showing the process in steps here.
Steps:
- First, select the data range.
- Then, go to Insert > Pivot Table
- Then there will open a new window.
- Here, select the Existing Worksheet option then select a cell in the location box to specify where to start the pivot table.
- Finally, press OK.
- Then, you will see a pivot table box created from the cell you specified and a window named PivotTable Fields will be created on the right side of the worksheet.
- Here, you will find the column names as a list.
- Now, drag the Dates option in the Rows box and the Sales Qty option to the Values box.
- Now, remove the Quarters option from the rows options.
- Then, move the Years option to the column box.
- Then, you will see a perfect Pivot table where you can see the Sales Qty with months in Rows and Years in Columns.
- Here, the values are already filtered by months and years.
- You can also specify a filter for month and year by clicking on the drop-down arrow on the cell Column Labels and Row Labels.
- Here, go to Column labels to select the Year and Row labels to select the Months.
4. Create a Button with VBA to Filter Dates by Month and Year
Using Excel VBA Macro does automate your work to a great extent. And, you can also use VBA code to filter dates by month and year in Excel. So, I am sharing the VBA code here and explaining it to you so you can modify the code for your data.
Steps:
- Before putting the code, make cells to insert the Start Date and End Date in the Filter Criteria box.
- So, if you want filter dates for June 2022, you will give the Start date as of 06/01/2022 and the End Date as of 06/30/2022.
- Now, go to the Developer tab in the top ribbon. Click on the Visual Basic option. You can also use ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window if you don’t have the Developer tab added.
- Then, the ‘Microsoft Visual Basic for Applications window will appear, and click on the Insert option here.
- And, select Module from the option.
- Now, a new ‘Module’ window will appear. And Paste this VBA code into the box.
Public Sub FilterDates() Dim Start_Date As Long, End_Date As Long Start_Date = Range("G3").Value End_Date = Range("G4").Value Range("B5:B12").AutoFilter field:=1, _ Criteria1:=">=" & Start_Date, _ Operator:=xlAnd, _ Criteria2:="<=" & End_Date End Sub
- To run the code go to the top menu, press on the ‘Run’ option, and here will open some other options and select the Macro to run also you can simply press shortcut F5 to run the code.
🔎 Explanation of VBA Code:
- Public Sub FilterDates()
That creates a Macro named FilterDates
- Dim Start_Date As Long, End_Date As Long
Declares two-variable named Start_Date and End_Date and the format is Long
- Start_Date = Range(“G3”).Value
Taking the value of Start Date from the cell G3
- End_Date = Range(“G4”).Value
Taking the value of End Date from the cell G4
- Range(“B5:B12”).AutoFilter field:=1
Declaring the range of cells where to apply filter by VBA AutoFilter function. Here you have to input the range of your Dataset.
- Criteria1:=”>=” & Start_Date
Setting the condition of the filter for the dates which are greater than the start date.
- Operator:=xlAnd
Putting And operator to add another condition of filter which will apply AND logic.
- Criteria2:=”<=” & End_Date
Setting the condition of the filter for the dates which are less than the end date.
- End Sub
Declaring the end of the Macro
- Also, you can add a Macro button so you don’t have to go to the module tab to run the code and filter.
- For this, go to the Developer tab > Insert and select the Button icon.
- Then, draw a box with the mouse at the position of the worksheet and give a name.
- After that, a window will appear, and select the Macro from the list then press OK.
- Now, you see a Button being created. Rename it to Filter Dates.
- Now, insert a suitable Start Date and End Date in the cells to filter the dates by month and year then press the Macro button.
Things to Remember
- You must edit the data range in the VBA code before applying it to your dataset.
- The Excel Filter option is the easiest way to apply a filter but it changes the main dataset.
- Using the Filter function or Pivot Table to apply the filter will be better if you want to keep the main dataset unchanged.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, I am sharing with you four easy methods to filter dates by month and year in Excel including VBA code. Also, you can download the free workbook to practice yourself. In addition, you can follow the formula breakdown and VBA code explanation to modify them for your data. And, I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
<< Go Back to Date Filter | Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
