Use Excel 2007 and 2010's built-it date filters when they're adequate. When they're not, try helper columns and date functions.
If you're using Excel to schedule events for any projects, chances are you filter those records by specific criteria. If you're using Excel 2007 or 2010, you're in luck, because there are several built-in date filters. In this post, we'll review a simple filter for dates falling within the current week. By current, we mean a Sunday through Saturday week. Then, we'll show you an advanced filter. (Neither of these techniques work in Excel 2003.)
The simple data set shown below comprises two date columns, a start and an end date. Some occur in the same week, some don't. Your first hurdle is to decide whether you're filtering by the start or end date. Neither choice is wrong; it just depends on your needs. To illustrate the simple filter, let's find all the records where the start date falls within the current week, as follows:
- First, select the entire data range by clicking in any cell in the data range and pressing [Ctrl]+[Shift]+8.
- Next, apply a simple filter by clicking the Data tab and then clicking Filter in the Sort & Filter group.
- Click the StartDate column's dropdown filter and choose Date Filters.
- Then, choose This Week from the resulting submenu.
The filtered group displays only those records where the StartDate value falls within the current week. Before, the filtered records reflect a current day of June 24, 2012 through June 30, 2012. You probably noticed that there are a number of date filters. They're convenient and easy to use-even for users with limited skills. As you might expect, you could apply the same filter to the EndDate column and get a different result (or not, depending on the date values).
To clear the filter, simply click Clear in the Sort & Filter group.
Now, let's suppose that you need to display only those records where both the start and end date fall within the current week. There are probably a half dozen ways to accomplish this task, but I recommend two helper columns and the WEEKNUM() function. This simple function uses the following form to return the week number:
Where datetext is a date string. For instance, the function WEEKNUM("6/27/2012") returns the value 26.
To adjust this simple data set for a more advanced filter, add two helper columns based on the following functions in cells D6 and E6, respectively:
=WEEKNUM(C6)Then, copy the functions to the remaining cells in each column.
The WEEKNUM() function in column D returns the week number for each start date. Similarly, the function in column E returns the week number for each end date. As you can see, the week numbers for the start and end dates aren't always the same for each record; sometimes the time period extends beyond the current week.
Next, copy the header cells to create a criteria range (A1:E1). Then, enter the following criteria expression into both D2 and E2:
Now you're ready to apply the advanced filter:
- Click the Data tab.
- In the Sort & Filter group, click Advanced Filter.
- In the List Range control, enter A5:E13.
- In the Criteria Range control, enter A1:E2.
- Click OK.
If you want to filter for records where either the start or end date falls within the current week, move one of the criteria expressions to row 3, and update the advanced filter's criteria range, accordingly. Placing the criteria in two different rows allows the filter to find records that satisfy either criteria rather than both.
As I mentioned, there are other ways to accomplish these filtering tasks. How would you do it?