Solved: Filtering Dates

[Aussiebear]

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]

I never knew about Custom Date filters -- thanks

I only have a brute force way which is to add 2 additional columns with

[VBA]
=IF(MONTH(A2)>6,"FYR"&YEAR(A2)+1,"FYR"&YEAR(A2))

and

=MONTH(A2)
[/VBA]

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]

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]

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]

I missed some formatting......

try this one Ted.

[Aussiebear]

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]

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]

Add a helper column and a table of week numbers

[Aussiebear]

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!

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]

MD, thank you for your file. I was wondering if the following was possible

[vba]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[/vba]

[Aussiebear]

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]

You had a slash missing after the 6 on end date but this works for me if there is a date in j3

[VBA]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
[/VBA]

[Aussiebear]

And so it does for me too, but it refuses to filter.

[Aussiebear]

"Refuses to filter" is a bit harsh as it filters out everything, even the data I wanted to see.

[lucas]

It works for me if you put a date in j3

see attached.

[Paul_Hossler]

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]

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]

"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]

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]

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?