» Filtering by the Date Field
CATEGORY - Excel Filter
VERSION - All Microsoft Excel Versions
1. If the List is in Filter mode, turn it off by selecting Filter, and then AutoFilter from the Data menu.
2. Copy the Date column.
3. Select two columns to the right of the Date field.
4. Press Shift+F10 and then press Ctrl++ or right click, and select Insert Copied Cells from the shortcut menu (pasting by inserting copied cells allows one to insert two columns, and paste the copied column into them).
5. Change the titles for the two columns to Month and Year.
6. Select the Month column and press Ctrl+1 to format the cells.
7. In the Number tab, select Custom.
8. In the Type box, enter the format mmmm (full month format), and click OK.
9. Repeat steps 6 and 7 for the Year column.
10. In the Type box, enter the format yyyy (full year format), and click OK. You can now filter by any month or year.
11. The List is now ready to filter according to Month and Year criteria.
Book Store:
Recommended Books:
- How to Pay Zero Taxes (Annual)
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- Absolute Beginner's Guide to Microsoft Office Excel 2003
- Special Edition Using Microsoft Outlook 2002
- Microsoft Outlook Version 2002 Step by Step (With CD-ROM)
- The Interpretation of Financial Statements
Sr. Estimator
M Ballard Posted on: 31-12-1969
In Excel 2000, the shortcut Shift-Spacebar selects the current Row. To select the Current Column, use Ctrl-SpacebarProject Manager
John Hensen Posted on: 31-12-1969
Please correct the text of the tip to read CTRL+Spacebar vice Shift+Spacebar.Autofilter by date
Terry O'Donnell Posted on: 31-12-1969
I tried to do this from within a macro so as to select data
from a > than to a < than range.
My date column was in the dd/mm/yyyy format.
I found that when I tried to do this with (dd/mm/yyyy)
selected from the dropdown table
I could not get the correct data from my worksheet
But when I changed the date format to mm/dd/yyyy within the macro and
without changing the format within the worksheet
it worked perfectly
Reply: Autofilter by date - Terry O'Donnell from Birmingham England wrote on August 11, 2003 3:41 PM EST
Alan Posted on: 31-12-1969
Hi Terry,
Sounds like maybe your date settings are up the spout!
It could be that someone has set your OS region to somewhere in the world that uses a funny order for the dates (I think French Canada uses Month / Day / Year - probably elsewhere too).
This might cause Excel to misinterpret the dates, and derive underlying date / time values that are not correct. I cannot be sure of course.
Assuming you are using MS Windows 2000 (you'll have to translate for other versions), try the followng:
Start
Settings
Control Panel
Regional Options
Select the Date Tab
Ensure that the short and long date formats are either DD/MM/YYYY or something similar but in that order.
If you want to be 'global' or perhaps more precisely, up to date (no pun intended) in a teccy sense, you can use:
YYYY/MM/DD
That last format of course actually makes far more sense that DD/MM/YYYY, but either has to be preferable to the somewhat confused MM/DD/YYYY that you seem to have at the moment.
If you are from French Canada (or elsewhere) - No offence intended. We all have our historical legacies to deal with, NZ is no different!
Alan.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
