Excel: Year/Month/Day- Custom AutoFilter for date columns

Excel: Year/Month/Day  -YOUR OBJECTIVE

You recorded your customers’ orders in an Excel worksheet and now want to display just the orders that were placed in March 2006. Since you are filtering for a range of dates, you must use a Custom AutoFilter for dates.

In this learning module “Excel: Year/Month/Day” , you will be shown how to use the AutoFilter for date columns.

Excel: Year/Month/Day  -HOW DOES IT WORK?

Activate the AutoFilter for your table by clicking the button on the Datatab, in the Sort & Filter group.

Now click the filter arrow on the “Order Date” column and select the Date Filters option. The submenu that opens shows date-related filter functions:

This command allows you to enter a precise date.

Clicking on any of the the options in this area, opens the Custom AutoFilter dialog box where you can enter dates as comparison criteria: Before… After… or Between… any dates you specify. Dates are expressed in the customary date format: month/day/year.

The options in this area allow you to enter date criteria relative to the current day. Since Excel knows the current system time and date, if you select the This Quarter option on 1/20/2010, for example, Excel would display all rows of data with dates that fall on or between 1/1/2010 to 3/31/2010.

The excel Year To Date option displays all rows containing dates that fall in the current year up to and including the current date. Given that Excel knows the system time and date, if you chose this command on 8/31/2010, all rows of data with dates from 1/1/2010 up to and including 8/31/2010 will be displayed.

This option filters entries that fall in a certain fixed period, without regard for the year in which the specified period falls. If you select All Dates in the Period -> May, for example, all data rows in the month of May will be displayed, regardless of whether the date is, for example: 05/01/2010, 05/14/2009 or 05/28/2008.

The Custom Filter option opens the Custom AutoFilter dialog box in which you can specify the comparison operators and the dates manually, and if you like, combine two filter criteria by using an AND or OR operator.

In the current example, select the Between… option to enter a specific range of dates yourelf. In the Custom AutoFilter dialog box, enter the start date 03/1/2006 into the upper entry box and 3/31/2006 into the lower entry box. Conclude by clicking OK. You can also select your dates with just your mouse: Just click the button to the right of the arrow button, and then click in the displayed calendar to select your date. Finish by clicking OK.

The filtered table will then display only orders placed in March 2006.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen