Filter date by month and year

[Peter]

I have a column of dates that I am trying to filter on just the month
and year.

Date
22/05/1981
16/03/1981
26/01/2006
01/05/1986

Is it possible to filter on only the last 4 digits of a cell?

Thanks,

Peter

 

[Ron de Bruin]

Hi Peter

You can insert a helper column with the worksheet function Year or Month
Or install EasyFilter
http://www.rondebruin.nl/easyfilter.htm

Or use advancedfilter, you only have to have one cell with the formula and use that cell in your criteria.

 

[Peter]

Hi Peter

You can insert a helper column with the worksheet function Year or Month
Or install EasyFilterhttp://www.rondebruin.nl/easyfilter.htm

Or use advancedfilter, you only have to have one cell with the formula and use that cell in your criteria.

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm





- Show quoted text -

Thank you but I am actually trying to tell the macro to look at the
last 7 digits of a cell and filter it by that. Is this possible?

 

[Ron de Bruin]

Yes with advanced file you can use this for Feb 2007

=AND(YEAR(A2)= 2007,MONTH(A2)=2)

A1 is the header cell and a2 is the first date
See Debra's site for more info
http://www.contextures.com/xladvfilter02.html

Easyfilter is much easier

 

[Ron de Bruin]

Oops you want a macro

Record a macro when you do this and insert the formula in the criteria cell with code

 

[Peter]

Sorry Ron, I'm a bit new to this.

Is "=AND(YEAR(A2)= 2007,MONTH(A2)=2) " the code?

 

[Peter]

Sorry Ron, I've tried recording the macro. It still doesn't make
sence. What is the code meant to look like?
BTY I really appreciate your help, this has been bugging me for days!

 

[Ron de Bruin]

I will post a example Peter

The filter year and month are they in a worksheet cell or always the same ?

 

[Peter]

The operator selects the month and the year from 2 list boxes on a
userform.
This is then turned into two variables.

I hope this makes sense, and thanks again!

 

[Ron de Bruin]

Hi Peter

Try this tester

Header in A6:C6
Dates in A7:A1000
data in B7:C1000

We use A1:A2 for the criteria
A1= always empty
a2 = the code insert the formula in this cell

On the user form ListBox2 = years and ListBox2 = months
Note: numbers and not Jan, Feb
If you want that you can use the index

The try this button on the userform


Private Sub CommandButton1_Click()

Range("A2").Formula = "=AND(YEAR(A7)= " & Me.ListBox2.Value & ",MONTH(A7)=" & Me.ListBox1.Value & ")"

Range("A6:C1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A2"), Unique:=False

End Sub