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:
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Beate Schmitz

Active Member
Joined
May 20, 2007
Messages
392
Hello,

This would be your sheet:
Excel Workbook
AB
1Criteria:Month ago:
25/23/20092
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,398
Messages
5,571,900
Members
412,423
Latest member
monbri9931
Top