How to Filter Dates by Month and Year in Excel (4 Easy Methods)

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 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.


Download Practice Workbook

You can download the practice workbook from here:


4 Methods to Filter Dates by Month and Year in Excel

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.

How to Filter Dates by Month and Year in Excel


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 Sort & Filter Tab.
  • Then, click on the Filter option.

How to Filter Dates by Month and Year in Excel

  • As a result, you will see a drop-down arrow will create in the header cell of the column.
  • Click on this to apply the filter as you want.

How to Filter Dates by Month and Year in Excel

  • After clicking there will open a window, here 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.

How to Filter Dates by Month and Year in Excel

  • Now, If mark the year 2022, and the months’ May and June from the filter list, you will get the filtered table as shown.

How to Filter Dates by Month and Year in Excel

Read More: How to Filter Last 30 Days of Date in Excel (5 Easy Ways)


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

How to Filter Dates by Month and Year in Excel

  • 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")
Here, the MONTH function works to extract the month number from the dates and the YEAR function works to extract the year value from the dates. And FILTER function is working in the range B5:D12, on the condition where the month value and the year are equal to the value of the given cells. And if the FILTER function won’t find any rows meeting the given conditions then it will give “N/A” as output.

How to Filter Dates by Month and Year in Excel

  • Enter the value in the month and year cell.
  • So, you will get the filtered output for the specific month and year.

How to Filter Dates by Month and Year in Excel

  • 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.

Read More: Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)


Similar Readings


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

Utilize Excel PIVOT Table Tool

  • 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.

Utilize Excel PIVOT Table Tool

  • Then, you will see a pivot table box will create from the cell you specified and a window named PivotTable Fields will create on the right side of the worksheet.
  • Here, you will find the column names as a list.

Utilize Excel PIVOT Table Tool

  • Now, drag the Dates option in the rows box and the Sales Qty option to the values box.

Utilize Excel PIVOT Table Tool

  • 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.

Utilize Excel PIVOT Table Tool

  • 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.

Utilize Excel PIVOT Table Tool

Read More: How to Use Pivot Table to Filter Date Range in Excel (5 Ways)


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 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 give the End Date as of 06/30/2022.

Create a Filter Button with VBA

  • Now, go to the Developer tab in the top ribbon. And 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.

Create a Filter Button with VBA

  • Then, the ‘Microsoft Visual Basic for Applications window will appear, and click on the Insert option here.
  • And, select Module from the option.

Create a Filter Button with VBA

  • 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.

Create a Filter Button with VBA


🔎 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 AutoFilter VBA 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.

Create a Filter Button with VBA

  • 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.

Create a Filter Button with VBA

  • Now, you see a Button will create. Rename it to Filter Dates.

How to Filter Dates by Month and Year in Excel

  • 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.

How to Filter Dates by Month and Year in Excel


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.

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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo