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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,512
Messages
5,625,235
Members
416,083
Latest member
LSUchamp06

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