PDA

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!

ZVI
04-17-2010, 08:28 PM
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?

ZVI
04-18-2010, 07:52 AM
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