Question: How do I filter the last xx years or xx months in Excel 2007?

How do I exclude the current month when using the year to date filter in Excel 2007?

Answer: Use advanced filter with criteria ranges. I have calculated the exact dates needed to create the criteria ranges.

I will go through the exact steps on how to accomplish the date filter.

The picture below shows the calculation of the dates, the criteria ranges and the list to filter.

filter-a-date-range-in-excel-20071

How to create a criteria range

  1. Copy the header of the list to a new location, see cell A16:C16 in the above picture.
  2. Type the criteria, see A17:B17 in the above picture.
  3. Formula in A17:
    ="<=2009-07-31" + ENTER
    Formula in B17:
    =">=2009-01-01" + ENTER

How to filter a list using a criteria range

  1. Click "Data" in the ribbon
  2. Click Advancedadvanced-filter
  3. Select List range: A27:C64advanced-filter2
  4. Select the criteria range A16:C17
  5. Click OK!

The new filtered list.

advanced-filter3

Repeat the above steps to filter the last xx years or xx months using the criteria ranges.

Download excel example file.
filter-a-list-of-dates.xlsx
(Excel 2007 Workbook *.xlsx)