AutoFilter with Dates - Date range, yes; single date, no

[Yamezz]

I'm trying to filter dates in column A that are in the format 09-Jul-24.
The following code will not return any records for a single date (9th of July), but will correctly filter for the entire month of interest (including 12 records for the 9th of July).
What am I doing wrong?

Sub TestDayFilter()

Dim EarningsDay As Long, StartDate As Long, EndDate As Long

EarningsDay = Sheets("Invoicing").Range("L1").Value

With Sheets("Invoicing").Range("L1")
        StartDate = DateSerial(Year(.Value), Month(.Value), 1)
        EndDate = DateSerial(Year(.Value), Month(.Value) + 1, 0)
        
End With

Sheets("Operations").Select

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
    
Selection.AutoFilter Field:=1, Criteria1:=EarningsDay
Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

End Sub

 

[Joe4]

Please post a sample of a data set that is not working as it should, so we can set it up exactly as you have it and try it for ourselves, and see the issue in action.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Yamezz]

Hello Joe. Here's a sanitised dataset.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								Volume (L)		Storage					$0.00	23.73	$1,574.50
2	Date	V	L	Bl	Ba	O	D	B4	After	Off	On	Addn	Rate	g/mL/#	Price	Time	Cost
3	16-Jun-24															0.33	$20.00
4	20-Jun-24															0.17	$10.00
5	20-Jun-24															0.10	$6.00
6	20-Jun-24															0.17	$10.00
7	28-Jun-24															1.50	$90.00
8	28-Jun-24															1.25	$75.00
9	28-Jun-24															0.33	$20.00
10	03-Jul-24															0.10	$6.00
11	03-Jul-24															0.10	$6.00
12	03-Jul-24															0.10	$6.00
13	03-Jul-24															0.10	$6.00
14	03-Jul-24															0.10	$6.00
15	03-Jul-24															0.10	$6.00
16	03-Jul-24															0.10	$6.00
17	03-Jul-24															0.10	$6.00
18	03-Jul-24															0.10	$6.00
19	03-Jul-24															0.10	$6.00
20	03-Jul-24															0.10	$6.00
21	03-Jul-24															0.17	$10.00
22	03-Jul-24																$37.50
23	04-Jul-24															0.10	$6.00
24	04-Jul-24															0.10	$6.00
25	04-Jul-24															0.10	$6.00
26	04-Jul-24															0.10	$6.00
27	04-Jul-24															1.33	$80.00
28	04-Jul-24															2.08	$125.00
29	05-Jul-24															0.10	$6.00
30	05-Jul-24															0.10	$6.00
31	05-Jul-24															1.67	$100.00
32	05-Jul-24															0.10	$6.00
33	05-Jul-24															0.10	$6.00
34	05-Jul-24															0.10	$6.00
35	05-Jul-24															0.10	$6.00
36	05-Jul-24															0.75	$45.00
37	05-Jul-24															0.10	$6.00
38	05-Jul-24															0.10	$6.00
39	06-Jul-24															1.45	$87.00
40	06-Jul-24															0.10	$6.00
41	06-Jul-24															0.10	$6.00
42	06-Jul-24															2.00	$120.00
43	06-Jul-24
44	06-Jul-24
45	06-Jul-24
46	06-Jul-24															0.10	$6.00
47	06-Jul-24															0.10	$6.00
48	06-Jul-24															0.10	$6.00
49	06-Jul-24															0.10	$6.00
50	06-Jul-24															1.00	$60.00
51	06-Jul-24															0.10	$6.00
52	06-Jul-24															0.75	$45.00
53	06-Jul-24															1.50	$90.00
54	06-Jul-24
55	06-Jul-24															0.10	$6.00
56	06-Jul-24															0.25	$15.00
57	06-Jul-24															0.50	$30.00
58	07-Jul-24															1.00	$60.00
59	09-Jul-24															0.50	$30.00
60	09-Jul-24															0.50	$30.00
61	09-Jul-24															0.50	$30.00
62	09-Jul-24															0.10	$6.00
63	09-Jul-24															0.10	$6.00
64	09-Jul-24															0.10	$6.00
65	09-Jul-24															0.10	$6.00
66	09-Jul-24															0.17	$10.00
67	09-Jul-24															0.10	$6.00
68	09-Jul-24																$88.00
69	09-Jul-24															0.17	$10.00
70	09-Jul-24																$25.00
Operations

Cell Formulas
Range	Formula
O1	O1	=AGGREGATE(9,7,O3:O99980)
P1:Q1	P1	=SUBTOTAL(9,P3:P999735)
P3,P9	P3	=2/6
P4,P69,P66,P21,P6	P4	=1/6
Q69,Q23:Q67,Q3:Q21	Q3	=IF(P3<>0,IF(F3="Winery Hire",25*P3,IF(OR(C3="H&L",C3="HFW"),40*P3,IF(C3<>"H&L",60*P3,""))),"")
P27	P27	=1+2/6-1/6+1/6
P28	P28	=2+5/60
P31	P31	=1+4/6
P36	P36	=25/60+2/6
Q68	Q68	=11*8

Book1
	L
1	09-Jul-24
Invoicing

 

[Joe4]

Try making the changes to your code as shown here (changes in red):

Sub TestDayFilter()

Dim EarningsDay As Long, StartDate As String, EndDate As String

EarningsDay = Sheets("Invoicing").Range("L1").Value

With Sheets("Invoicing").Range("L1")
        StartDate = Format(DateSerial(Year(.Value), Month(.Value), 1), "m/d/yyyy")
        EndDate = Format(DateSerial(Year(.Value), Month(.Value) + 1, 0), "m/d/yyyy")
        
End With

Sheets("Operations").Select

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
    
Selection.AutoFilter Field:=1, Criteria1:=EarningsDay
Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

End Sub

 

[Yamezz]

Thanks for looking at this Joe. The problem step in my code is the filter for a single date. The date range filter works fine as-is. Running the code you supplied produces the same problem as my version.

 

[Alex Blakenburg]

Selection.AutoFilter Field:=1, Criteria1:=EarningsDay

To use that line the date format has to match the format used in the column exactly.
For example this would work.

Selection.AutoFilter Field:=1, Criteria1:=Format(EarningsDay, Sheets("Operations").Range("A3").NumberFormat)

Its a high risk approach since I have seen date columns that have been appended to having inconsistent formatting applied.

Since you already have EarningsDay declared as long this will work and would be a safer approach.

Selection.AutoFilter Field:=1, Criteria1:=">=" & EarningsDay, Operator:=xlAnd, Criteria2:="<" & EarningsDay + 1

 

[Yamezz]

Thank you Alex. I used your last suggestion.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.