Help with 30, 60 , 90 Days overdue using date range

ma3kBurns

New Member
Joined
Sep 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,
First post so if I get it wrong feel free to educate me. I know enough VBA to be dangerous but not skilled by any means.
I have a Macro I created that filters dates by values from a "Due Date" column. If I run the Macro the results are coded from the current date. so Date-1 gives me 30 days late, Date-31 gives me 60 days late and Date-61 gives me 90 days late. It works just fine.
I'm trying to do the same thing for a Date Range so that if you run it on Monday it will pull all of the above but the program needs to do it for Sunday, Saturday, and Friday, or whatever the date range is.
I think I need to loop through each of the days but I can't find the solution.
Can anyone assist me?

Here is what I have to capture the date range:

Dim W1Startdate As Date, W1Enddate As Date

Selection.NumberFormat = "mm/dd/yyyy"

W1Startdate = Application.InputBox("Enter the Start Date:")

W1Enddate = Application.InputBox("Enter the End Date:")

ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=W1Startdate", Operator:=xlAnd, Criteria2:="<=W1Enddate"


Here is what I have for the SINGLE DATE that works but I need it to do the same thing for whatever dates are in the range:

'This pulls data for 30 Days Late

Dim rng30 As Range

Dim autofiltrng30 As Range


With ActiveSheet

.Range("A1").AutoFilter Field:=3, Criteria1:= _

"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"

.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 1 'This is where I need the code to pull 3 dates


'This pulls data for 60 Days Late

Dim rng60 As Range

Dim autofiltrng60 As Range



With ActiveSheet

.Range("A1").AutoFilter Field:=3, Criteria1:= _

"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"

.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 31 ' This is where I need the code to pull 3 dates


'This pulls data for 90 Days Late

Dim rng90 As Range

Dim autofiltrng90 As Range



With ActiveSheet

.Range("A1").AutoFilter Field:=3, Criteria1:= _

"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"

.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 61 'This is where I need the code to pull 3 dates
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

The date variable (starDate, endDate) must be outside the quotation marks.

It is difficult to work with dates and filters, sometimes it must be string, sometimes date and even double type.
I tested the following and it works for me.

VBA Code:
Sub test()
  Dim sDate As Date, eDate As Date
  sDate = Application.InputBox("Enter the Start Date: ")
  eDate = Application.InputBox("Enter the End Date: ")
  ActiveSheet.Range("A1").AutoFilter 6, ">=" & Format(sDate, "mm/dd/yyyy"), xlAnd, "<=" & Format(eDate, "mm/dd/yyyy")
End Sub
 

ma3kBurns

New Member
Joined
Sep 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
DanteAmor,
Thank you for the assistance. This works for today's date because my input boxes' end date is the day before the current date so 30 days late is today -1 or what I was using, Date - 1.

Later in my code I have a sheet created that also does the same thing, or it's supposed to, but it is Date -31, for 60 days late and even further in my code it is Date - 61 for 90 days late.
Entering a single date it calculates just fine for 30, 60, and 90 Days late.
Your code is capturing ALL the 30 Days late because it's grabbing everything between the start date and the end date.
MY code is capturing the 60 and 90 Days late for ONLY the eDate because my code simply reads Date -31 and Date -61 for the output. How do I I incorporate 30, 60, and 90 days late for ALL the dates entered from sDate to EDate?


With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 31 <------- 'HERE IS WHERE I PUT YOUR CODE BUT I NEED IT "-31", AND "-61" TO CAPTURE ALL OF THE DATES BUT I CAN'T GET IT TO WORK

I hope that makes sense to you.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I hope to understand.
Assuming you have this data before filtering.
varios 14sep2020.xlsm
ACFHI
1ACDATEDays overdue
2A7Overdue21-jun-86
3A8Overdue25-jun-82
4A9Overdue29-jun-78
5A10Overdue03-jul-74
6A11Overdue07-jul-70
7A12Overdue11-jul-66
8A13Overdue15-jul-62
9A14Overdue19-jul-58>= - 61
10A15Overdue23-jul-54
11A16Overdue27-jul-50
12A17Overdue31-jul-46
13A18Overdue04-ago-42
14A19Overdue08-ago-38
15A20Overdue12-ago-34<= - 31
16A21Overdue16-ago-30
17A22Overdue20-ago-26
18A23Overdue24-ago-22
19A24Overdue28-ago-18
20A25Overdue01-sep-14
21A26Overdue15-sep0<-- today
Sheet1a


After filtering:
varios 14sep2020.xlsm
ACFHI
1ACDATEDays overdue
9A14Overdue19-jul-58>= - 61
10A15Overdue23-jul-54
11A16Overdue27-jul-50
12A17Overdue31-jul-46
13A18Overdue04-ago-42
14A19Overdue08-ago-38
15A20Overdue12-ago-34<= - 31
Sheet1a


If the above is correct then:
VBA Code:
Sub test2()
  With ActiveSheet
    .Range("A1").AutoFilter 3, "=Overdue", xlOr, "=Registration Flagged"
    .Range("A1").AutoFilter 6, ">=" & Format(Date - 61, "mm/dd/yyyy"), xlAnd, "<=" & Format(Date - 31, "mm/dd/yyyy")
  End With
End Sub

The macro above works for me.
If the above is not what you need, then you could show with examples what you need. Use the XL2BB tool to paste ranges from your sheet here. (see my signature)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top