Filter Data By Date Field

The use of Microsoft Excel’s Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft excel.

Filter Data by Date Field using FILTER Function

The General Formula is as below:

=FILTER(Total_data,MONTH(range2)=5,"Not Found")

filter by date field1

Summary

FILTER function can be used with one of Excel’s date functions to make your data more relevant. In this example shown below, E2 is used:

=FILTER(A2:C9,MONTH(B2:B9)=5,"Not Found")

filter by date field1

This is a great way to only show data from one specific Month. For example, it would be easy to want our report for May only! We need to get this effect out of the filter engine. (A2:C9) and then add “date” after that with B2:B9 named ranges, so no other periods show up when looking through your findings.

Clarification:

The MONTH function is used to create an argument for the FILTER function that filters based on a logical test created with ONE of many other functions available in Excel. The array data contains all information, without any headers or spaces between values, perfect if you want every record exactly how it’s shown!

=MONTH(B2:B9)

filter by date field1

Month contains a range from B2:B9. Since this is an array with 8 cells, MONTH returns 8 results!

=MONTH(B2:B9)=5

filter by date field1

The “include” argument is a set of values determining which results will be included in the array. These are compared to 5, and this operation creates an ordered list with TRUE or FALSE as its elements before delivering it on through filter function call.

Only when the result is TRUE will it appear in this final output. The “if_empty” argument has been set to ‘ Not Found ‘ so as not to show any unmatched records or values.”

Filter By Month And Year

To filter by Month and year, use the following formula:

=FILTER(A2:C9,(MONTH(B2:B9)=5)*(YEAR(B2:B9)=2022),"Not Found")

filter by date field1

The values for Month and year can easily be replaced with cell references.

Filter the Data by Date using Format Cell

Now we’ll look at how to filter the data by date. We have information in range A1: C9, with dates represented as columns on each line representing who purchased what from us over time.

filter by date field1

To change the filter for a specific month or year, follow these steps:

Step1: Copy A1:A9 and paste it right next to column A

Step2: Click on the range A1:A9 and select

Step3: To copy the date, press Control+C on your keyboard.

filter by date field1

Step4: Select cell A1 and press Ctrl+Shift+”+”

filter by date field1

Step5: The Insert Dialog Box will appear.

Step6: To continue editing the data, choose Shift Cells right and click the Ok button.

filter by date field1

Step7: To increase the range of cells that you can paste into, copy and then press Ctrl+ shift +. Then select shift cells right in the insert dialog box by clicking OK.

filter by date field1

Step8: A new month and year are just around the corner, so it’s time to update your headlines!

filter by date field1

Step9: Select the column of Month and then press ctrl+1 keys to open the format cells dialog box

Step10: Once the dialog box appears, then select custom in the number tab.

filter by date field1

Step11: Enter mm (to show Month’s full name) in the type box and click on OK.

filter by date field1

Step12: Now select the Column of Year and press the Ctrl+1 to open the format cells dialog box.

Step13: Once the dialog box appears, select custom in the number tab.

Step14: In order to show the full year, just enter YYYY in this box and click on.

filter by date field1

Step15: The output is a simple list that can be sorted by either Month or year.

filter by date field1

Step16: You can also use this trick to filter any cell quickly. Select the desired range and press Ctrl+Shift+L.

If you want to filter the data according to a specific month or year, select cell A1 and press Ctrl+Shift+L.

filter by date field1

Conclusion:

We’ve given you the tools to filter your data in Microsoft Excel by date fields in this blog post. Now that you know how easy it is to do, why not try adding a few more filters to your workbook? You may be surprised what insights can come from applying multiple filters at once!

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
Related Posts

Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Add Months To Date In Excel
Add Months To Date1

It is important to adjust time periods when performing financial modeling. In Excel, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you ...

Add Days to Date in MS Excel
add days to date1

If you are an vavid MS Excel user, then you might have come across situations where you need to add the same or different days into the particular date, and if you have done this task manually, then let me ...

How to Count Dates of Given Year in Excel
count dates of given year7

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Date & Time Format to Date in Excel
Convert Date & Time Format to Date 7

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you ...

How to Extract Year from Date & Time Format in Excel
Extract Year from Date & Time Format 6

Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are ...

How to Calculate Remaining Days in a Month or Year in Excel
calculate remaining days5

This post will guide you how to calculate remaining days in a given month or year in Excel. How do I calculate the number of days left in a month or year using a formula in Excel 2013/2016. Calculate Remaining ...

How to Split Date into Day, Month and Year in Excel
split date into day month year10

This post will guide you how to split date into separate day, month and year in excel. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel. Split Date into ...

How to sort Dates by Month and Day Only in Excel
sort dates by month7

This post will guide you how to sort dates or birthdays by month or day only in Excel. How do I sort dates by Year or Month or Day only with a formula in Excel 2013/2016. How to use the ...

Sidebar