Filtering by the Date Field in Microsoft Excel

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.

img1

If we want to put the filter as per the Month and year, we need to follow below given steps:-

  • Copy the range A1:A18 and paste it right side to Column A
  • Select the date range A1:A18.
  • Copy the date column by pressing the key Ctrl+C on your keyboard.

img2

  • Select the cell A1 and press the key Ctrl++.

img3

  • Insert Paste dialog box will appear.
  • Select Shift cells right and click on OK.

img4

  • Again, copy the range and press the Ctrl++, and select shift cells right in the insert paste dialog box and click on OK.

img5

  • Change the title for 2 columns to Month and year.

img6

  • Select the Month Column and press the Ctrl+1.
  • Format cells dialog box will appear.
  • Go to number tab and select custom.
  • In the type box, enter the format mmmm (to show the full name of month) and click on OK.

img7

img8

  • Select the Year Column and press the Ctrl+1.
  • Format cells dialog box will appear.
  • Go to number tab and select custom.
  • In the type box, enter the format YYYY (to show the full year) and click on OK.

img9

img10

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.

img11

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.

img12

img13

This is the way we can put the filter by the date field in Microsoft Excel.

Users are saying about us...

  1. "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 "

  2. "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."

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube