VBA Filter - Between Today's date + and - Days

[sg1990]

I've got a date attribute in "dd\mm\yyyy" format in a sheet and I'm trying write a macro, which filters plus minus 7 days from TODAYS date.

For example If today's date is 14/06/2016 my filter range will be 07/06/2016 to 21/06/2016.

I specifically need help with adding and subtracting from today's date and displaying the filter.

 Dim tMinusSeven As String
    tMinusSeven = Date
 
Dim tPlusSeven As String
    tPlusSeven = Date          


        ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.<wbr>AutoFilter field:=11, Criteria1:=">=" & tMinusSeven -7[COLOR=#333333], _

Operator:=xlAnd, Criteria2:=[/COLOR]"<=" & tPlusSeven -7

 

[baitmaster]

welcome to the board

You're using a text string which will give you problems. As a general rule I always use the underlying number behind todays date: 26 May 2016 = 42516. Reason I use the number without formatting is due to bad experiences switching between US and UK date formats i.e. days and month getting swapped, but I digress

Declare your variable as a long rather than a string, =clng(date) and work with that

 

[sg1990]

Thanks for your reply. I'd prefer to keep the date format.

The real issue I'm having is adding or subtracting values from the variables I'm declaring as Dates

 

[dmt32]

Hi,
Pass the date to a long variable using the DateSerial function where you can adjust the plus & minus days as required.

 Sub FilterDates()

        Dim StartDate As Long, EndDate As Long
    
        StartDate = DateSerial(Year(Date), Month(Date), Day(Date) - 7)
        EndDate = DateSerial(Year(Date), Month(Date), Day(Date) + 7)
        
    
        ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=11, _
                                                Criteria1:=">=" & StartDate, _
                                                Operator:=xlAnd, _
                                                Criteria2:="<=" & EndDate
End Sub

Dave

 

[baitmaster]

Thanks for your reply. I'd prefer to keep the date format.

The real issue I'm having is adding or subtracting values from the variables I'm declaring as Dates

You misunderstand. You will still be working with the date, and any time you write this "long" value into a cell that is formatted as a date, you'll see that date. I'm trying to help you understand that a date is simply a number that is formatted in a specific way within Excel. Work with Date or work with the number - but one way occasionally has problems

And you aren't declaring variables as dates, you're declaring them as strings using e.g.

Dim tMinusSeven As String

. That's why your calculation isn't working, because VBA thinks you're now dealing with text

DateSerial(Year(Date), Month(Date), Day(Date)) is just recreating Date, by splitting it into it's components and then joining them back together. You can just use date +7

 

[sg1990]

And you aren't declaring variables as dates, you're declaring them as strings using e.g.

I see. That does clear things up. New to programming, but I understand the concept.

and yes Date + or - 7 does do the trick.

Thank you.

 

[sg1990]

Hi,
Pass the date to a long variable using the DateSerial function where you can adjust the plus & minus days as required.

 Sub FilterDates()

        Dim StartDate As Long, EndDate As Long
    
        StartDate = DateSerial(Year(Date), Month(Date), Day(Date) - 7)
        EndDate = DateSerial(Year(Date), Month(Date), Day(Date) + 7)
        
    
        ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=11, _
                                                Criteria1:=">=" & StartDate, _
                                                Operator:=xlAnd, _
                                                Criteria2:="<=" & EndDate
End Sub

Dave

Thank you works a treat.

Also mentioned below Date + or - 7 works as well.

cheers!

 

[dmt32]

Thank you works a treat.

Also mentioned below Date + or - 7 works as well.

cheers!

Hi,

Glad you have problem resolved - the DateSerial Function is invaluable where Windows Regional Settings are not US date format - Autofilter can when filtering dates, sometimes give unexpected results as Excel sees the dates as US date – passing the date to a long variable using DateSerial has for this reason, more purpose than just recreating Date, by splitting it into its components and then joining them back together – it is one way to ensure Autofilter works as intended.

Dave

 

[Lawrence569]

Hello everyone,

What should I add to the code above inorder to be able to autofilter the dates of Friday, Saturday and Sunday if it currently Monday whereas for the rest of the days it would just autofilter Today's date - 1?

Thank you very much in advance!