Date Filter in Excel

By
To explain “Date Filter“ in detail, we have a dummy data of some electronics products sold in 04 regions East, West, South and North from 1st Jan 2019 to 31st Dec 2021.



Date filter can only be applied for the column which contains Date values. In our case we can apply Date filter for the Date column. Click on the drop down button which is available on header cell “Date” and click on “Date Filters” option as highlighted in below image.




We can filter data available in this Date column based on below criteria’s:



So let us now see each of the criteria in detail. 

Please make a note that we will consider today’s date as “13th September 2020” throughout this post. So all the output results shown in this blog post will depend on this date.

Equals: 

This option will help us to filter the dates exactly equal to our input date. For example if we want to filter the data for date 1-Jan-20 then we will provide our “equals” criteria as shown in below image.



This will provide us the output as shown in below image which filters all data for date 1-Jan-20.




Before:

This option will help us to filter the dates which occurs before the input date. For example if we want to filter the data for dates before 10-Jan-19 then we will provide our “is before” criteria as shown in below image.



As an output, we can see the data is filtered out only for dates which occurs before 10-Jan-19.



After:

This option will help us to filter the dates which occurs after the input date. For example if we want to filter the data for dates after 24-Dec-21 then we will provide our “is after” criteria as shown in below image.



As an output, we can see the data is filtered out only for dates which occurs after 24-Dec-21.




Between:

This option will help us to filter the dates which occurs between two input dates. For example if we want to filter the data for dates which occurs between 1-May-20 and 15-May-20 then we will provide our “between” criteria as shown in below image.



As an output, we can see the data is filtered out only for dates which occurs between 1-May-20 and 15-May-20.



Tomorrow:

This option will help us to filter the tomorrow’s date based on date & time settings of your computer. Select input option “Tomorrow” as shown in below image.



As a result, data for tomorrow’s date i.e 14th September 2020 will get filtered out as shown in below image.




Today:

This option will help us to filter the today’s date based on date & time settings of your computer. Select input option “Today” as shown in below image.



As a result, data for today’s date i.e. 13th September 2020 will get filtered out as shown in below image.




Yesterday:

This option will help us to filter the yesterday’s date based on date & time settings of your computer. Select input option “yesterday” as shown in below image.



As a result, data for Yesterday’s date i.e. 12th September 2020 will get filtered out as shown in below image.




Next Week:

This option will help us to filter the dates which will occur in the next week. Select this input as shown in below image.



As a result, data for Next Week’s dates (from 20th September 2020 to 26th September 2020) will get filtered out as shown in below image.




This Week:

This option will help us to filter the dates which are occurring in current week. Select this input as shown in below image.



As a result, data for This Week’s dates (from 13th September 2020 to 19th September 2020) will get filtered out as shown in below image.



Last Week:

This option will help us to filter the dates which have occurred in last week. Select this input as shown in below image.



As a result, data for Last Week’s dates (from 6th September 2020 to 12th September 2020) will get filtered out as shown in below image.



Next Month:

This option will help us to filter the dates which will occur in next month. Select this input as shown in below image.



As a result, data for Next Month’s dates (from 1st October 2020 to 31st October 2020) will get filtered out as shown in below image.




This Month:

This option will help us to filter the dates which are occurring in current month. Select this input as shown in below image.



As a result, data for This Month’s dates (from 1st September 2020 to 30th September 2020) will get filtered out as shown in below image.




Last Month:

This option will help us to filter the dates which have occurred in last month. Select this input as shown in below image.



As a result, data for Last Month’s dates (1st August 2020 to 31st August 2020) will get filtered out as shown in below image.




Next Quarter:

This option will help us to filter the dates which will occur in next quarter. Select this input as shown in below image.



As a result, data for Next Quarter’s dates (from 1st October 2020 to 31st October 2020 will get filtered out as shown in below image.





This Quarter:

This option will help us to filter the dates which are occurring in this quarter. Select this input as shown in below image.



As a result, data for This Quarter’s dates (from 1st July 2020 to 30th September 2020) will get filtered out as shown in below image.




Last Quarter:

This option will help us to filter the dates which have occurred in last quarter. Select this input as shown in below image.



As a result, data for Last Quarter’s dates (1st April 2020 to 30th June 2020) will get filtered out as shown in below image.


Next Year:

This option will help us to filter the dates which will occur in next year. Select this input as shown in below image.



As a result, data for Next Year’s dates (1st January 2021 to 31st December 2021) will get filtered out as shown in below image.


This Year:

This option will help us to filter the dates which are occurring in current year. Select this input as shown in below image.



As a result, data for This Year’s dates (from 1st January 2020 to 31st December 2020) will get filtered out as shown in below image.



Last Year:

This option will help us to filter the dates which have occurred in last year. Select this input as shown in below image.



As a result, data for Last Year’s dates (1st January 2019 to 31st December 2019) will get filtered out as shown in below image.



Year to Date:

This option will help us to filter the dates from the 1st day of the current year to the today’s date. Select this input as shown in below image.



As a result, data for Year to Date (from 1st January 2020 to 13th September 2020) will get filtered out as shown in below image.




All the Dates in Period: 

This option will help us to filter the dates from the below criteria:

Quarter 1: This option will help us to filter the dates which occurs in “Quarter 1” irrespective of the year.

Quarter 2: This option will help us to filter the dates which occurs in “Quarter 2” irrespective of the year.

Quarter 3: This option will help us to filter the dates which occurs in “Quarter 3” irrespective of the year.

Quarter 4: This option will help us to filter the dates which occurs in “Quarter 4” irrespective of the year.

January: This option will help us to filter the dates which occurs in month of “January” irrespective of the year.

February: This option will help us to filter the dates which occurs in month of “February” irrespective of the year.

March: This option will help us to filter the dates which occurs in month of “March” irrespective of the year.

April: This option will help us to filter the dates which occurs in month of “April” irrespective of the year.

May: This option will help us to filter the dates which occurs in month of “May” irrespective of the year.

June: This option will help us to filter the dates which occurs in month of “February” irrespective of the year.

July: This option will help us to filter the dates which occurs in month of “July” irrespective of the year.

August: This option will help us to filter the dates which occurs in month of “August” irrespective of the year.

September: This option will help us to filter the dates which occurs in month of “September” irrespective of the year.

October: This option will help us to filter the dates which occurs in month of “October” irrespective of the year.

November: This option will help us to filter the dates which occurs in month of “November” irrespective of the year.

December: This option will help us to filter the dates which occurs in month of “December” irrespective of the year.


Custom Filter:

This option will help us to filter the dates based on custom criteria’s. Select this input as shown in below image.



As a result, data as per our custom filter criteria dates will get filtered out as shown in below image.



I hope you are now very much clear with all the concepts of “Date Filter in Excel”. Use these filter appropriately and be a smart worker in excel.
Microsoft Certified Excel Expert, Enthusiastic to teach excel tricks and basic excel concepts which will help to save time and efforts in our daily office work.

Comments

  1. AnonymousOctober 12, 2022 at 11:19 PM

    Hi Tejraj. I have a file where team members capture time worked on various tasks, by date. The file is hosted on Box and team members edit the file online. In the Excel online version, many of the date filters have stopped working. For example, if you were to select Date Filters --> This Week, rather than filtering to entries for this week, nothing happens.

    However, if I download the file and open it with desktop Excel, these filters work just fine.

    I have tried checking the date formats, regional settings, and using the text-to-columns feature.

    Any idea why this function would work just fine in the desktop version but not the online version, especially when it used to work online?

    Thanks,

    Daniel

    ReplyDelete

Post a Comment

Related Topics..

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of “Fill” command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Let’s take an example. We have inserted a text “Tejraj” in a cell “D5”. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Let’s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select ...
Read more

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number “1” in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number “1” will automatically fill down in the entire cell range . ...
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...
Read more

How to Remove Duplicate Entries from Multiple Columns in Excel

By
Image
In our last blog post, we have learned to remove duplicates entries in excel . In this blog post, we will go little advanced to remove the duplicate entries from multiple columns. Removing Duplicates Entries from 02 Columns: We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the “Fruits” column and “Price/Kg” column are highlighted with different colors. To remove duplicate entries in these 02 columns, follow the steps provided below: 1. Select the data from which we want to remove the duplicates. 2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image. 3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen. 4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name o...
Read more

Excel Pivot Table

By
Image
Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a...
Read more

Exploring Font Colors and Fill Colors in Excel

By
Image
Many times we use colors in our excel files to make our work attractive. We can apply colors to the texts as well as to the cells.  If we want to change the color of text available in cells then we use the various color options available from “font color” command. Similarly, if we want to change the color of cells itself then we use the various color options available in “fill color” command. But have you ever wondered that what are names of different colors available which we can apply to texts and cells?? YouTube Video In this post we will see details about this.  The color options which are available in “font color” and “fill color” commands are as below: Fill Color: Font Color: If we compare above two images, we can see these color options are categorized into two categories. 1. Theme Colors 2. Standard Colors Theme Colors: There are total 60 different colors available in (06 rows x 10 columns) in Theme Colors category  (These colors w...
Read more

Insert Multiple Rows in Excel Based On Cell Value

By
Image
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code. For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”. For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on. To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds. To do this, just follow the below steps: 1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here . Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image. 2. Now, click on the “Visual Basic” command as highlighted in below image. 3. Once ...
Read more

Conditional Formatting:Highlighting Above Average and Below Average Values

By
Image
This blog post is continuation to our series of posts on “Conditional Formatting”. In this blog post, we will learn about highlighting values which are “Above Average” and “Below Average” of the selected values. To learn this post in an easy way, I have randomly listed few numbers as shown in below image. We will learn how to highlight the numbers which are above the average of all the selected numbers and which are below the average of all the selected numbers. For your easy understanding I have already calculated average value of all these numbers numbers (Which is 65). This will help us to easily cross check our output. Highlighting Above Average Values: Select all the values from which we wish to highlight the “Above Average Values”. Go to Conditional Formatting (which is available in Home Tab under Styles group) and select “Above Average” option as shown in below image. “Above Average” dialog box will appear on screen. Select the Formatting style from available list and hit OK but...
Read more

Bold, Italic and Underline Commands from Font Group

By
Image
Bold, Italic and Underline… These three commands are very basic but very important and frequently used in excel. These commands can be found under Font Group. YouTube Video: Let us have detailed description about these commands..!                                              Bold : If we want to change our text into Bold format, then we can use this command. Keyboard shortcut to use this command is Ctrl+B. In below image we have inserted a sample text in cell A1. If we click on the Bold icon from font group or use keyboard shortcut Ctrl+B, then the text from cell A1 will convert into Bold as shown in below image. Italic : If we want to convert our text into Italic format, then we can use this command. Keyboard shortcut to use this command is Ctrl+I. In below image we have inserted a sample text in cell A1. If we click on the Italic icon...
Read more

Number Filter in Excel

By
Image
In our previous posts we have seen about filters . In case you missed to read about what are filters and how to apply filter then click here to read in detail. In this blog post we will learn how to apply “Number Filters” in excel. We will see all the sub types of Number Filters in detail with below example. For this we have taken a data of some electronic products sold in East, West, South and North regions from 1st Aug 2019 to 1st Apr 2021 with us. Number filters can be applied to column which contain number values. So in our case we can apply number filters to column “No of Items Sold”. Click on drop down button which you will find on header cell “No of Items Sold” and you will see the “Number Filters” option as highlighted in below image. We can now filter the data present in this column based on below criteria’s: Equal Does Not Equal Greater Than Greater Than Or Equal To Less Than Less Than Or Equal To Between Top 10 Above Average Below Average Custom Filter Now, we will explai...
Read more