View Full Version : Solved: Filtering Dates
Aussiebear
03-24-2010, 07:15 PM
In Excel 2007 I would like to filter a column of dates to show just those rows of data that fall within a selected financial year. Could someone show me how this is done please?
I can setup manually a financial year by using Date Filters, Custom Filter and enter a Start date & a Finish date, but I was hoping to have the option of selecting it from the options available under Date Filters.
Also can I then select particular month within a predetermined financial year?
If this is not possible, can it be setup with VBA?
Paul_Hossler
03-24-2010, 07:33 PM
I never knew about Custom Date filters -- thanks
I only have a brute force way which is to add 2 additional columns with
=IF(MONTH(A2)>6,"FYR"&YEAR(A2)+1,"FYR"&YEAR(A2))
and
=MONTH(A2)
in them and filter on that
I have US Gov Fiscal Year UDF, but it's nothing more than the formula in VBA and also used in a seperate column
Paul
lucas
03-24-2010, 07:44 PM
I see that Paul has posted but I looked this up for you so I'll post it.
Ted, I don't know if it will help or not but in the first textbox of the userform type 1/7/2006 and it will retreive the dates for 365 days ahead.....
Aussiebear
03-24-2010, 07:47 PM
Thanks Paul. I had gone back and extended the date range to include dates from additional years just to further experiment. Excel will then offer filter ranges based on years, with subfilters based on months.
Sadly it seems that they didn't allow filtering on financial years.
lucas
03-24-2010, 08:54 PM
I missed some formatting......
try this one Ted.
Aussiebear
03-24-2010, 11:06 PM
I misses some formatting......
try this one Ted.
Thanks Steve, the real data will be some 9 columns wide and a growing number of rows,so referring to a static range may be an issue. Off to work now, so will be unable to test and or experiement with your code further for some time.
Bob Phillips
03-25-2010, 02:48 AM
If you saved your data in a SQL Server DB, then created a SQL Server Data Warehouse, and then created an Analysis Services cube, you could add a financial year dimension to that, and then pivot the cube to look at either calendar year or financial year - easy peasy!
mdmackillop
03-25-2010, 11:58 AM
Add a helper column and a table of week numbers
Aussiebear
03-25-2010, 03:45 PM
If you saved your data in a SQL Server DB, then created a SQL Server Data Warehouse, and then created an Analysis Services cube, you could add a financial year dimension to that, and then pivot the cube to look at either calendar year or financial year - easy peasy!
:jail: I ask for an excape stategy, and Bob gives me Einstein's theory of relativity in 4 different languages......... (Sigh) He is so English.
Aussiebear
03-25-2010, 11:36 PM
MD, thank you for your file. I was wondering if the following was possible
Sub AnalysisServicesCube1()
Dim Yr As Long, StartDate As Date, EndDate As Date, Crit1 As String, Crit2 As String
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Columns(1).AutoFilter
Exit Sub
End If
Yr = Range("j3")
StartDate = "1/7/" & Yr
EndDate = "30/6" & (Yr + 1)
Crit1 = ">=" & StartDate
Crit2 = "<" & EndDate
ActiveSheet.Columns(1).AutoFilter field:=1, Criteria1:=Crit1, Criteria2:=Crit2, Operator:=xlAnd
End Sub
Aussiebear
03-26-2010, 02:34 PM
Any Ideas? Somehow the Values for StartDate & EndDate show as 12:00 time value rather than 1/7/year chosen or 30/6/year chosen
lucas
03-26-2010, 02:49 PM
You had a slash missing after the 6 on end date but this works for me if there is a date in j3
Sub AnalysisServicesCube1()
Dim Yr As Long, StartDate As Date, EndDate As Date, Crit1 As String, Crit2 As String
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Columns(1).AutoFilter
Exit Sub
End If
Yr = Range("j3")
StartDate = "1/7/" & Yr
MsgBox StartDate
EndDate = "30/6/" & (Yr + 1)
MsgBox EndDate
' Crit1 = ">=" & StartDate
' Crit2 = "<" & EndDate
'
' ActiveSheet.Columns(1).AutoFilter field:=1, Criteria1:=Crit1, Criteria2:=Crit2, Operator:=xlAnd
'
End Sub
Aussiebear
03-26-2010, 03:06 PM
And so it does for me too, but it refuses to filter.:dunno
Aussiebear
03-26-2010, 03:08 PM
"Refuses to filter" is a bit harsh as it filters out everything, even the data I wanted to see.
lucas
03-26-2010, 03:52 PM
It works for me if you put a date in j3
see attached.
Paul_Hossler
03-26-2010, 05:58 PM
Aussiebear --- can I ask how you intend to use this?
I get the impression that it's a reatively simple list that you want to filter
... the real data will be some 9 columns wide and a growing number of rows
So far it looks like there's not any Aussie Fiscal Year (Jul-Jun) filter options (like there's no US Gov fiscal year (Oct-Sep) options).
If you were going to use a pivot table or something equivalent, I've found that a help column (as someone called it) using a UDF or a WS formula gives the most flexibility, and has the advantage of being unambigiously selectable (FY2010, FY2011, etc.)
Just my two cents
Paul
Aussiebear
03-26-2010, 10:54 PM
Paul, Once I gain an understanding of this concept, I will be applying it to share portfolio data ( particularly dividend data). I will need to be able to filter out a predetermined financial year,so a copy can be sent off to the Accountants.
Bob Phillips
03-27-2010, 03:52 AM
"Refuses to filter" is a bit harsh as it filters out everything, even the data I wanted to see.
Make sure that the criteria is formatted the same as the data, autofilter is very picky with dates. (I still say an OLAP cube is best :))
Paul_Hossler
03-27-2010, 10:29 AM
I will be applying it to share portfolio data ( particularly dividend data). I will need to be able to filter out a predetermined financial year,so a copy can be sent off to the Accountants.
I like to seperate the data from the processing. So another approach to think about would be to
1. Have all user data on one sheet
2. One Sub to read data sheet, check completeness, format, etc. (dates are dates, required numbers are entered, etc.)
3. Another Sub to select, copy and format to seperate Output sheet; only right year would go over, data re-arranged, etc.
4, Format output sheet
Just thoughts
Paul
Aussiebear
03-27-2010, 01:48 PM
Make sure that the criteria is formatted the same as the data, autofilter is very picky with dates. (I still say an OLAP cube is best :))
..... Ok I give up.....
How Bob?
Pinokkio
03-27-2010, 01:56 PM
Maibe this can help?
http://www.contextures.com/excelfiles.html#Filter
FL0006 - Filter for Date Range -- uses Data Validation and Advanced Filter to extract a list of orders in selected date range; file contains a macro which automates the filter
Aussiebear
03-27-2010, 10:38 PM
Thanks. I've already looked at this file.
Bob Phillips
03-28-2010, 07:45 AM
The way I do it is to get the format of the first value in the range being filtered.
Something like this
With ActiveSheet.Columns(1)
.AutoFilter field:=1, _
Criteria1:=Format(Crit1, .Cells(2, 1).NumberFormat), _
Criteria2:=Format(Crit2, .Cells(2, 1).NumberFormat), _
Operator:=xlAnd
End With
Aussiebear
03-31-2010, 09:10 PM
I'm obviousily not seeing the woods for the trees here, but this code is failing
at the setting of the advanced filter stage... what am I missing out on?
Sub Filter_HoldingData()
Dim LastRow As Long
Dim Yr As Long
Dim StartDate As Date
Dim EndDate As Date
Dim Crit1 As String
Dim Crit2 As String
Yr = Range("L2")
StartDate = "1/7/" & Yr
EndDate = "30/6/" & Yr
Crit1 = ">=" & StartDate
Crit2 = "<=" & EndDate
With Worksheets("Dividends")
Application.ScreenUpdating = False
If Range("L2") <> "" Then
.Range("C2").Value = Crit1
.Range("D2").Value = Crit2
End If
With ActiveSheet.Columns(2).AutoFilter
LastRow = Cells(Rows.Count, "B").End(xlDown).Row
.Range("B6").Resize(LastRow - 5, 11).AdvancedFilter , Action:=xlFilterInPlace, _
CriteriaRange:=.Range("B1:M2"), Unique:=False
End With
Application.ScreenUpdating = True
End With
End Sub
Am I mixing Autofilter with Advanced Filter?
Aussiebear
04-17-2010, 12:30 PM
As a workaround I am now trying to advance filter, but am "having issues" with filtering the data on dates. Currently this setup filters on every wanted criteria other than those dealing with dates Cols B & N.
Use the button to bring up the form to set your filter requirements. Textbox #2 allows the User to either enter a specific date or a financial year. Depending on which option you choose the correct position in the criteria fields is chosen. But then it won't filter on the dates bit. Just flashes the screen.
mdmackillop
04-17-2010, 02:01 PM
Hi Ted
Give this a try. I took the liberty of adding a three combos for ease of use and more importantly, got the dates working!
Because filter works with Text property of the cell instead of the Value one, I would suggest to change format of the date columns and date conditional cell(s) to "yyyy-mm-dd;@" before filtering, with restoring it to initial “m/d/yyyy” format after filtering.
Aussiebear
04-18-2010, 02:25 AM
@MD: Thankyou. Its been driving me up the wall. Now I can have a beer in peace and stop cursing Excel.
@ZVI: Thank you also for your advice. I hadn't realised that filter only works on text rather than value. Can you elaborate some more please on the format suggested?
"yyyy/mm/dd;@" what does this do?
Aussiebear,
After some testing I have replaced format "yyyy/mm/dd;@" by "yyyy-mm-dd" one in my previous suggestion.
Try this code for your example Filter Dates.xls of post #1
Sub AnalysisServicesCube1()
Dim StartDate As Date, EndDate As Date, Crit1$, Crit2$, Yr&
' Temporary format for correct dates filtering
Const NumFt = "yyyy-mm-dd"
With Sheets(1)
' Define date threshold
Yr = 2006 ' <-- Change to suit, for example to Yr = .Range("J1")
StartDate = DateSerial(Yr, 7, 1)
EndDate = DateSerial(Yr + 1, 6, 30)
' Set filter criteria with NumFt format
Crit1 = ">=" & Format(StartDate, NumFt)
Crit2 = "<" & Format(EndDate, NumFt)
' Filter the dates
.AutoFilterMode = False
.Columns(1).AutoFilter Field:=1, Criteria1:=Crit1, Operator:=xlAnd, Criteria2:=Crit2
End With
End Sub
Vladimir
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.