| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter date by month and year
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter" wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
On 6 Feb, 10:02, "Ron de Bruin" wrote:
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 "Peter" wrote in ooglegroups.com... 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- Hide quoted text - - 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter" wrote in message ups.com... On 6 Feb, 10:02, "Ron de Bruin" wrote: 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 "Peter" wrote in ooglegroups.com... 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- Hide quoted text - - 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
Oops you want a macro
Record a macro when you do this and insert the formula in the criteria cell with code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter" wrote in message ups.com... On 6 Feb, 10:02, "Ron de Bruin" wrote: 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 "Peter" wrote in ooglegroups.com... 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- Hide quoted text - - 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
Sorry Ron, I'm a bit new to this.
Is "=AND(YEAR(A2)= 2007,MONTH(A2)=2) " the code? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
I will post a example Peter
The filter year and month are they in a worksheet cell or always the same ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter" wrote in message oups.com... 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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter date by month and year
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter" wrote in message oups.com... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date(Year,Month,Day) not returnign correct date | Excel Discussion (Misc queries) | |||
| count month when date is in day/month/year format | Excel Worksheet Functions | |||
| =DATE(YEAR(P4),MONTH(P4)+1,1) | Excel Worksheet Functions | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
![Peter[_61_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
