Using AdvancedFilter for Dates

[Zacariah171]

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

 

[John_w]

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:

Advanced Filter Between Dates — Excel Dashboards VBA

The use of the Advanced Filter Between dates to isolate data with Excel vba

www.thesmallman.com

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]

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.

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
	P	Q
3	Date
4	November	FALSE
Timesheet Table

Cell Formulas
Range	Formula
Q4	Q4	=TEXT(A7,"mmmm")=P$4

 

[John_w]

Thanks Peter, your solution is simpler and works for me.

 

[Peter_SSs]

.. and works for me.

Thanks for the confirmation John.

 

[Zacariah171]

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?

 

[Peter_SSs]

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?

 

[Zacariah171]

This is what I have so far. I think if something is entered into A4, it should take priority over P4.

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]

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

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

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]

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?