VBA filter transactions for last quarter data and 1 day before

[Knockoutpie]

The below snipped captures transaction dates for the last quarter, which is almost perfect, but i need to modify to capture 1 day beyond the last quarter...
Can anyone help me modify that?

Example, Last quarter data is Apr 01 to Jun 30, but I really need Mar 31 to Jun 30.

    ActiveSheet.Range("$A$1").AutoFilter Field:=2, Criteria1:=11, _
        Operator:=11, Criteria2:=0, SubField:=0

 

[kevin9999]

Please try the following on a copy of your workbook. Just change "Sheet1" to the actual sheet name.

Option Explicit
Sub Knockoutpie()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Dim StartD As Long, EndD As Long
    StartD = DateSerial(Year(Date), (((Month(Date) - 4) \ 3) * 3) + 1, 0)
    EndD = DateSerial(Year(Date), (((Month(Date) - 1) \ 3) * 3) + 1, 0)
    
    With ws.Range("A1").CurrentRegion
        .AutoFilter 2, ">=" & CLng(StartD), 1, "<=" & CLng(EndD)
    End With
End Sub

Before:

Book1
	A	B	C
1	hdr1	Date	hdr3
2	data	10/03/2023	data
3	data	17/03/2023	data
4	data	24/03/2023	data
5	data	31/03/2023	data
6	data	10/04/2023	data
7	data	20/04/2023	data
8	data	30/04/2023	data
9	data	10/05/2023	data
10	data	20/05/2023	data
11	data	30/05/2023	data
12	data	09/06/2023	data
13	data	19/06/2023	data
14	data	30/06/2023	data
15	data	09/07/2023	data
16	data	19/07/2023	data
17	data	29/07/2023	data
18	data	08/08/2023	data
Sheet1

After:

Book1
	A	B	C
1	hdr1	Date	hdr3
5	data	31/03/2023	data
6	data	10/04/2023	data
7	data	20/04/2023	data
8	data	30/04/2023	data
9	data	10/05/2023	data
10	data	20/05/2023	data
11	data	30/05/2023	data
12	data	09/06/2023	data
13	data	19/06/2023	data
14	data	30/06/2023	data
19
Sheet1

 

[Bosquedeguate]

Please try the following on a copy of your workbook. Just change "Sheet1" to the actual sheet name.

Option Explicit
Sub Knockoutpie()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Dim StartD As Long, EndD As Long
    StartD = DateSerial(Year(Date), (((Month(Date) - 4) \ 3) * 3) + 1, 0)
    EndD = DateSerial(Year(Date), (((Month(Date) - 1) \ 3) * 3) + 1, 0)
   
    With ws.Range("A1").CurrentRegion
        .AutoFilter 2, ">=" & CLng(StartD), 1, "<=" & CLng(EndD)
    End With
End Sub

Before:

Book1
	A	B	C
1	hdr1	Date	hdr3
2	data	10/03/2023	data
3	data	17/03/2023	data
4	data	24/03/2023	data
5	data	31/03/2023	data
6	data	10/04/2023	data
7	data	20/04/2023	data
8	data	30/04/2023	data
9	data	10/05/2023	data
10	data	20/05/2023	data
11	data	30/05/2023	data
12	data	09/06/2023	data
13	data	19/06/2023	data
14	data	30/06/2023	data
15	data	09/07/2023	data
16	data	19/07/2023	data
17	data	29/07/2023	data
18	data	08/08/2023	data
Sheet1

After:

Book1
	A	B	C
1	hdr1	Date	hdr3
5	data	31/03/2023	data
6	data	10/04/2023	data
7	data	20/04/2023	data
8	data	30/04/2023	data
9	data	10/05/2023	data
10	data	20/05/2023	data
11	data	30/05/2023	data
12	data	09/06/2023	data
13	data	19/06/2023	data
14	data	30/06/2023	data
19
Sheet1

I did some testing on your QTR filters. It works for 2nd,3rd,&4th, but not for 1st QTR dates
If your input date is 1/1/2023 - 3/31/2023 the
StartDate should be 9/30/2022
EndDate should be 12/31/2022
Your code is generating for Feb and March dates (2023)
StartDate 12/31/2022
EndDate(s) are OK

This is the code I used and tested

'note replace dt in the code below with Date for the AutoFilter function
StartDate = DateAdd("m", -3, DateAdd("d", -1, DateSerial(Year(dt), Int((Month(dt) - 1) / 3) * 3 + 1, 1)))
EndDate = DateAdd("d", -1, DateSerial(Year(dt), (Int((Month(dt) - 1) / 3) * 3 + 1), 1))

 

[kevin9999]

It works for 2nd,3rd,&4th, but not for 1st QTR dates

And yet when I test it, it does return the correct dates...

Option Explicit
Sub Knockoutpie_Test()
    Dim StartD As Long, EndD As Long, testD As Long
    testD = 44927   '<~~ *** numerical equivalent to 1/1/2023 ***
    StartD = DateSerial(Year(testD), (((Month(testD) - 4) \ 3) * 3) + 1, 0)
    EndD = DateSerial(Year(testD), (((Month(testD) - 1) \ 3) * 3) + 1, 0)

    Debug.Print Format(StartD, "dd/mm/yyyy")
    Debug.Print Format(EndD, "dd/mm/yyyy")
End Sub

 

[kevin9999]

This is the code I used and tested

I note you're testing different code to what I posted in post #2 and you're getting a different result. I'm not sure I'm following your point.

 

[Bosquedeguate]

And yet when I test it, it does return the correct dates...

Option Explicit
Sub Knockoutpie_Test()
    Dim StartD As Long, EndD As Long, testD As Long
    testD = 44927   '<~~ *** numerical equivalent to 1/1/2023 ***
    StartD = DateSerial(Year(testD), (((Month(testD) - 4) \ 3) * 3) + 1, 0)
    EndD = DateSerial(Year(testD), (((Month(testD) - 1) \ 3) * 3) + 1, 0)

    Debug.Print Format(StartD, "dd/mm/yyyy")
    Debug.Print Format(EndD, "dd/mm/yyyy")
End Sub

I plugged it into Excel 2016 and had issues

Book1
	E	F	G	H
1	Date	Prev Q Start	Prev Q End	Expected Q start
2	1/1/2023	9/30/22	12/31/22	9/30/2022
3	2/1/2023	12/31/22	12/31/22	9/30/2022
4	3/1/2023	12/31/22	12/31/22	9/30/2022
5	4/1/2023	12/31/22	3/31/23
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=qstartdate(E2)
G2:G5	G2	=QendDate(E2)

Function QStartDate(dt)
  QStartDate = DateSerial(Year(dt), (((Month(dt) - 4) \ 3) * 3 + 1), 0)
' QStartDate = DateAdd("m", -3, DateAdd("d", -1, DateSerial(Year(dt), Int((Month(dt) - 1) / 3) * 3 + 1, 1)))
End Function

Function QEndDate(dt)
  QEndDate = DateSerial(Year(dt), (((Month(dt) - 1) \ 3) * 3 + 1), 0)
'  QEndDate = DateAdd("d", -1, DateSerial(Year(dt), (Int((Month(dt) - 1) / 3) * 3 + 1), 1))
End Function

 

[kevin9999]

It certainly works in 365 - which I note the OP has indicated they are running.

 

[Bosquedeguate]

Weird!

 

[Peter_SSs]

And yet when I test it, it does return the correct dates...

Kevin, test it with 44960 (3 Feb 23)

 

[kevin9999]

Kevin, test it with 44960 (3 Feb 23)

Aaaahhh! I get 31 Dec for both. Doh!