Home / Dates / Filtering Dates in Multiple Columns

Filtering Dates in Multiple Columns

The ExcelHelpDesk Support team received the following request for help:

Problem – Unable to filter information by month and year with multiple columns

 

I am in need of a way to filter the information in the attached file. I need to be able to filter the entire sheet to show all row’s that have a specified month/year no matter which column the specified month/year is in.

This is used for my monthly report to show all after sales activity for each month. For example, at the end of the month I need to be able to filter all activities for the month of May 2014 to show all emails, phone calls, start-ups, acct notify, one year follow ups so I can convert that to PDF and send to the CEO. Please help!

 

The most familiar feature for filtering data is the Autofilter as it is very simple and painless to use. However, it is limited and does not really support filtering multiple columns that are required in this case. There are different approaches to accomplish the task. We could use full VBA coding by looping through all the rows and columns or a VBA-less method using built-in conditional functions such as IF and OR. In this post, we have opted to use a combination of VBA and Autofilter.

 

Step 1 – Prepare fields and additional column

 

We will insert a new column to the most right of the table. Let’s put Has Month/Year for the header title. Add month and year field at the top of the new column header just like in the screenshot.

 

Has Month/year column header

 

The initial value for the month is March, which is indicated as 3, and year is 2014. It is more convenient to define the values in these two cells so we will be able to change them a lot easier later rather than modifying again the values in all of each row.

 

Step 2 – Create the function

 

Click the Developer > Visual basic to open the visual basic editor. If you can’t see the Developer tab, make sure it is checked in File > Options > Customize Ribbon. Now we have the editor opened, let’s insert a new module by clicking the icon shown below then selecting Module.

Insert new module

 

After the module is created, enter the following code:

Public Function hasMonthYear(intMonth As Integer, intYear As Integer) As Boolean
 
    'declare the variables
    Dim strValue        As String
    Dim i               As Integer
    Dim intCurrentRow   As Integer
    Dim intCurrentCol   As Integer
 
    intCurrentRow = Application.Caller.Row      'the row number where the function is called
    intCurrentCol = Application.Caller.Column   'the column number where the function is called

    'this loops from the first column up to the column before where the function is entered.
    For i = 1 To intCurrentCol - 1
 
        'storing it to variable is faster since we will be using this value more than once
        strValue = Cells(intCurrentRow, i).Value
 
        'check first if the value is a date so we won't be wasting time and also to avoid unnecessary error
        If IsDate(strValue) Then
 
            'if it matches the month and year, immediately exit the loop and return true
            If Month(DateValue(strValue)) = intMonth And Year(DateValue(strValue)) = intYear Then
                hasMonthYear = True
                Exit For
            End If
 
        End If
    Next
 
End Function

 

Step 3 – Insert the hasMonthYear function

 

Go to the first cell under the Has Month/Year column and enter the function we created.

Insert function

T5 stands for the cell value of the month and T6 is for the year but if we try to drag the cell to copy the formula to other cells, Excel will automatically increment the cell values and will result to =hasMonthYear(T6,T7), =hasMonthYear(T7, T8), and so on. To avoid this, we must tell Excel that we do not want to adjust the cell references by putting a dollar sign next to the cell number. So let’s modify the previous function parameters we entered and change it to:

Insert function

Now, drag it and copy the formula to other cells until the end of the table. The function returns TRUE if it found the month and year in the dates in all columns at least once. Otherwise, it returns FALSE.

 

Step 4- Filter the data

 

The final step is to filter the Has Month/Year column. Select the column header and click Home tab > Sort & Filter > Filter. Click the down arrow of the Has Month/Year column then ensure that TRUE is the only one checked in the filter list to display all the rows that match your criteria. You can try changing the values of month and year, or modifying the VBA code to further meet your needs.

Filtered data

 

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

 

 

About Excel Help Desk

One comment

  1. Great help and awesome response time! Thanks!