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

 

[Peter_SSs]

Do I need to add the sheet name in the ranges?

In the Worksheet_Change code, No.

I can't seem to get the sheet's module to run.

In the images, row 2 appears blank. Is that actually the case or not?
In a vacant cell on that sheet, what does this formula return?
=COUNTA(A2:G2)

 

[Zacariah171]

Correct, row 2 is blank. That formula returns a 0. I'm just using that row to create space.

 

[Zacariah171]

ha, I apologize. It is working... The only thing I can think of that's different is that I actually closed Excel. I opened it and it is working now. Thank you so much for your help!

 

[Peter_SSs]

ha, I apologize. It is working... The only thing I can think of that's different is that I actually closed Excel. I opened it and it is working now.

Sounds like you 'events' (eg Worksheet_Change) had become disabled. Closing & reopening Excel would reset that.

Thank you so much for your help!

You're welcome.