Using AdvancedFilter for Dates

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
I have a timesheet with dates in column A, starting in A7, with a header, "Date", in A6. In A4 I have a dropdown menu with the dates that are being used in my Date column (there is a header in A3, "Date") and if I select one of the dates from the dropdown menu, everything filters just fine. I'd like to try and apply a second criteria to filter the dates by month. I added a second dropdown menu in cell P4, with a header, "Date", in P3 and used a list of months: January, February, etc. The way I have it, the "month" filter is being applied but isn't working because my dates don't match "January". My dates are formatted like 1/31/2020. Is there a way to filter dates that are in that format by using the name of the month? I also tried reformatting my dates to January 31, 2020 but my January filter still didn't work because they didn't match exactly. Basically, what I'm trying to achieve is leaving my date formatting as 1/31/2020 and having one criteria filter by the specific date and a second criteria filter by the month. Is this possible? Pleas let me know if more information is needed. Any advice is greatly appreciated!

'This filters the column by the date. I found this code from Excel Macro Mastery.
Sub AdvancedFilter()
Dim rgData As Range
Dim rgCriteria As Range
Set rgData = ThisWorkbook.Worksheets("Timesheet Table").Range("A6").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Timesheet Table").Range("A3").CurrentRegion
rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub

'This is what I thought should filter the column by the month.
Sub AdvancedFilterByMonth()
Dim rgData As Range
Dim rgCriteria As Range
Set rgData = ThisWorkbook.Worksheets("Timesheet Table").Range("A6").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Timesheet Table").Range("P3").CurrentRegion
rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub

Format 1
Date
12/30/2019
12/30/2019
12/30/2019
12/30/2019
12/30/2019
12/30/2019
1/2/2020
1/3/2020
1/3/2020
1/3/2020
1/3/2020
1/3/2020
1/3/2020

Format 2
Date
December 30, 2019
December 30, 2019
December 30, 2019
December 30, 2019
December 30, 2019
December 30, 2019
January 2, 2020
January 3, 2020
January 3, 2020
January 3, 2020
January 3, 2020
January 3, 2020
January 3, 2020
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,707
Is there a way to filter dates that are in that format by using the name of the month? I also tried reformatting my dates to January 31, 2020 but my January filter still didn't work because they didn't match exactly. Basically, what I'm trying to achieve is leaving my date formatting as 1/31/2020 and having one criteria filter by the specific date and a second criteria filter by the month. Is this possible?
Yes it is possible indirectly, using TRUE and FALSE criteria:


Suppose all the years are 2020, then the formula for your 'From' date (first day of the month) would be =DATEVALUE("1-"&$P$4&"-2020"). And the formula for your 'To' date (last day of the month) would be =EOMONTH(DATEVALUE("1-"&$P$4&"-2020"),0)

However because you have 2 different years I think you would also need a Year dropdown, or include the year in the Month dropdown, and modify those formulas accordingly.

Also, rather than the "mmdd" in the =TEXT formulas shown on that web page, you should probably use "yyyymmdd".
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
See if this works for you. In Q4 (or any other vacant cell that also has a vacant cell above) enter the formula shown below, then try this code to do the filter for month. It will show dates in the month from any year but I'm guessing from your attempted code that is either not possible with your data or it is what you want anyway?

BTW, please use code tags when posting code. My signature block has help on that.

VBA Code:
Sub AdvancedFilterByMonth()
  Dim rgData As Range
  Dim rgCriteria As Range
  
  Set rgData = ThisWorkbook.Worksheets("Timesheet Table").Range("A6").CurrentRegion
  Set rgCriteria = ThisWorkbook.Worksheets("Timesheet Table").Range("Q3:Q4")
  rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub

A_MrExcel.xlsm
PQ
3Date
4NovemberFALSE
Timesheet Table
Cell Formulas
RangeFormula
Q4Q4=TEXT(A7,"mmmm")=P$4
 

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
Thank you Peter and John for your responses. Sorry for my late response; I've been traveling. I also apologize for not being clear in my op. I have a table, headers are in A6:G6. All of my dates are in column A and can be listed as they are in my original post. I have one filter in A4 for Date. There, I can select a specific date from it's dropdown list and it will filter my table to only show the rows that have that specific date. So, if I select 1/3/2020 in A4, then only the rows with that date will show up in my table. That part is working great. I'm trying to do the same thing but using the month. So, if I select January from a dropdown list, say in P4, it will filter my table to only show all the rows that have any January date. I've added a screenshot of what I'm working with. Will I be able to use the AdvancedFilter option this way? Or will I have to basically just loop through each row to check the date and take that long route?

1580787496032.png


1580787984044.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you looking to have this filter happen automatically if a value is entered in P4?
If so, do you already have code that is doing an automatic filter when/if A4 is changed? If so could we see the code?

What should happen if a value is entered in P4 while there is already a value in A4 or vice-versa?
 
Last edited:

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
This is what I have so far. I think if something is entered into A4, it should take priority over P4.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Sheet2.Range("A3").CurrentRegion.Rows.Count = 1 And Sheet2.Range("P3").CurrentRegion.Rows.Count = 1 Then
        ClearFilter
        ElseIf Range("A4") <> "" Then
            If Not Application.Intersect(Target, Sheet2.Range("A3").CurrentRegion) Is Nothing Then
                AdvancedFilter
            End If
        ElseIf Range("P4") <> "" Then
            If Not Application.Intersect(Target, Sheet2.Range("P3").CurrentRegion) Is Nothing Then
                AdvancedFilterByMonth
            End If
    End If

End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
Try this in a copy of your workbook.

Keep the single formula in Q4 that I suggested in post #3 (you can hide that column if you don't want the formula result visible)

In the Sheet's module

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range
  
  Set Changed = Intersect(Target, Union(Range("A3").CurrentRegion.Resize(2), Range("P4")))
  If Not Changed Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ClearFilter
    If Changed.Address = "$P$4" Then
      If Not IsEmpty(Changed.Value) Then
        Range("A3").CurrentRegion.Rows(2).ClearContents
        AdvancedFilterByMonth
      End If
    Else
      Range("P4").ClearContents
      AdvancedFilter
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub


In a Standard module ..

VBA Code:
Sub ClearFilter()
  On Error Resume Next
  ActiveSheet.ShowAllData
End Sub


Sub AdvancedFilter()
  Dim rgData As Range
  Dim rgCriteria As Range
  
  Set rgData = ThisWorkbook.Worksheets("Timesheet Table").Range("A6").CurrentRegion
  Set rgCriteria = ThisWorkbook.Worksheets("Timesheet Table").Range("A3").CurrentRegion
  rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub


Sub AdvancedFilterByMonth()
  Dim rgData As Range
  Dim rgCriteria As Range
  
  Set rgData = ThisWorkbook.Worksheets("Timesheet Table").Range("A6").CurrentRegion
  Set rgCriteria = ThisWorkbook.Worksheets("Timesheet Table").Range("Q3:Q4")
  rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub
 

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
I can't seem to get the sheet's module to run. When I make a change to A4:G4 or to P4, nothing happens. Do I need to add the sheet name in the ranges?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,145
Messages
5,623,003
Members
415,945
Latest member
Koboca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top