If you have a range of date, and if you want to filter the data as per year or month, you can do it by using the Format Cells and Auto filter option in Microsoft Excel.
In this article, we will learn using Format Cells to format the date in Month or Year as per the filtration requirement.
Let’s take an example to understand how we can filter the data by the date.
We have data in range A1: C18. Column A contains date, column B contains customer name and column C contains Revenue amount.
If we want to put the filter as per the Month and year, we need to follow below given steps:-
Now list is ready to filter the data according Month and year criteria.
To insert the Auto Filter, select the cell A1 and press the key Ctrl+Shift+L. And filter the data according to the month and year.
This is the way we can put the filter by the date field in Microsoft Excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"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."
"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 "
Please correct the text of the tip to read CTRL+Spacebar vice Shift+Spacebar.
In Excel 2000, the shortcut Shift-Spacebar selects the current Row. To select the Current Column, use Ctrl-Spacebar