Filtering data based on start and end date, ignoring the year.

[hurter8]

Hi peeps,

I've got a problem I need some help with . In my workbook I have a sheet with a large amount of daily data for the past 25 years. I want an end user to be able to insert a Start and End date (e.g. 2016/07/01 to 2016/09/30) in another sheet, after which by the press of a button, all data between 1 July and 30 Sep over the past 25 years is copied to that sheet. So it should filter based on day and month only, ignoring the year.

I have attached the workbook. "Big data" is is where the data should be copied from and "Structure 1" is where it should be pasted to.

Thank you in advance!

[DBY]

Hello welcome to the forum.
The attached copy of your file amended for macros, contains and Advanced Filter Macros which brings back all the days and months selected in you Start and End dates ignoring years.

Hope you can amend this for your actual data. The range selection in the filter uses Current Region, which allows you to add data to the bottom of the list without adjusting the macro code. However, you must keep a column and row clear around this otherwise it loses the Current Region.

DBY

[hurter8]

Thank you! There's a issue though when I enter say 01-Aug to 01-Sep. It only gives me the 1st of Aug and 1st of Sep, leaving out 2-31 Aug. Do mind looking at that please?

untitled.JPG

[DBY]

So it should filter based on day and month only, ignoring the year.

Yes I see. I misunderstood. I thought you wanted only the day(s) ie. the 1st and the month(s). So the 1/8 to 1/9 will bring back all of the 1st and 8th. dates in the given months. I'll make some adjustments so it's between those two dates and get back.

[DBY]

Try the following formula in the 'Criteria' cell:

Please Login or Register  to view this content.

In my test it brings back all the dates between the Start and End dates for all years. The filter in effect changes the year in Date column (not actually) to whatever year is in the Start date (doesn't matter which year) and then brings back all the matching rows in the actual data.

DBY

[hurter8]

It works perfectly. Thank you!

[hurter8]

Hi DBY,

Is there any way you can help so that I can select a start date in one year and an end date in the next? e.g. 15 December to 10 January, and filter accordingly?

Currently it only gives me the data for the period that falls within the first year (15 Dec to 31 Dec).

Thank you in advance.