How to filter based on date?

XLstooge

New Member
Joined
Jul 23, 2009
Messages
10
Hi newbie here,
I don't have any macro knowledge but I am trying to create a macro that filters transaction records based on their aging days.
I need to filter transaction older than 2 months, 3 months, and so on. I don't know if it is possible to put the aging calculation on macro but I tried to calculate by excel worksheet formula and copy its value to my macro, no success.
Would somebody please give some knowledge. Been working on this for 4 days. :confused:
 

Beate Schmitz

Active Member
Joined
May 20, 2007
Messages
392
Hello,

This would be your sheet:
Excel Workbook
AB
1Criteria:Month ago:
2 2
3**
4**
5**
6Date*
77/13/2009*
87/18/2009*
93/18/2009*
106/13/2009*
113/28/2009*
124/16/2009*
Sheet


Put formula in A2 and number of months to be filtered back from today in B2. Autofilter is in cell A6.


Then you can use this code:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><pre><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> Test()
Selection.AutoFilter Field:=1, Criteria1:=<span style="color:#800000"; >">"</span> & CDbl(Range(<span style="color:#800000"; >"A2"</span>).Value)
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></span></pre></div>

By changing cell B2 the autofilter-code will show different results depending on the value of this cell.

Respectively, if you only want to show dates more than e.g. 2 months ago, the operator in the code has to be amended:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><pre><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> Test2()
Selection.AutoFilter Field:=1, Criteria1:=<span style="color:#800000"; >"<"</span> & CDbl(Range(<span style="color:#800000"; >"A2"</span>).Value)
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></span></pre></div>
 
Last edited:

XLstooge

New Member
Joined
Jul 23, 2009
Messages
10
Beate, thank you for quick response.
Can you explain what does this syntax do:
"CDbl(Range("A2").Value)" ?

I am still working on this using this macro:

Dim myDate As Date
myDate = CLng(Date) - 60
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="<" & myDate, Operator:=xlAnd

I think I'm getting good result filtering 2 months old transaction although I'm not sure what's this "Operator:=xlAnd" does.
 

Beate Schmitz

Active Member
Joined
May 20, 2007
Messages
392
Hello,

you don't need the "Operator:=xlAnd" because you only filter for one criteria.

By the way, have you tested my example? The advantage is, that you can easily change the time frame by entering a different value in B2.

To understand CDbl search for "type conversion function". It is too difficult for me to explain it in English.
 

XLstooge

New Member
Joined
Jul 23, 2009
Messages
10
Beate,
Thank you very much. I tried your method, it sure does have the luxury to change aging period as easy as typing a simple number!
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top