Filtering to a Date Range in the Past

Summary: If you have a large number of data records, each with an associated date, you might want to filter that data so you see only the records from within the past month or so. You can do this easily by using the technique described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Bill has a data table with fifty columns and many, many records. One of the columns contain a record date, with dates that range over the past three years. Bill asked if there was a way to create a filter criteria to display only those records with dates between 91 and 98 days ago.

One solution is to add a new column to your data table that indicates if the record date is in the desired timeframe. Assuming the record date is in column A, the following would work:

=AND(TODAY()-A2>=91,TODAY()-A2<=98)

The result is either a True or False value, depending on the record date. You could then set up the filtering based on the value of the new column. (You can use AutoFilter for this purpose.) Only those records with a True in the column (those between 91 and 98 days old) would be displayed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3306) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Organize Your Data! Using the powerful sorting capabilities of Excel can help you get your data into just the order you need. Find out how you can use the full capabilities of sorting to your benefit.

Check out ExcelTips: Serioius Sorting today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Filtering to a Date Range in the Past