How to Filter Between Two Dates in Google Sheets

We often need to know about just some specific range of data from a sea of data. Like, a company has data of all the information of several years but what an employee of that company needs to know is about only some specific data which are within a specific range of dates. Should he find them one by one? The FILTER function along with some other functions in Google Sheets helps to filter between two dates just within the blink of eye.


A Sample of Practice Spreadsheet

You can download the spreadsheet used to demonstrate examples in this article.


4 Quick Methods to Filter Between Two Dates in Google Sheets

We will use the following sample dataset to demonstrate these methods accurately. The dataset represents the records of an online shop from May 2022 to August 2022.

google sheets filter between two dates


1. Using FILTER Function Manually

The FILTER function in Google Sheets works within a range of cells according to the given criteria and returns an array of values after filtering. Let’s assume, in the following dataset above, we want to filter all the data from the date 5/20/2022(M/D/Y) to 7/20/2022.

Steps:

  • First, select Cell B4. At the toolbar, select “Create a filter”.

Using FILTER Function Manually to filter between two dates in google sheets

  • The table will then look like this-

  • Now, select the dashed icon in Cell B4 beside “Order Date”, then select “Filter by condition”, after that select “Is between”.

Using FILTER Function Manually to filter between two dates in google sheets

  • Type “ 5/20/2022” in the first empty box and then “7/20/2022” in the second. Finally, select “OK”.

Using FILTER Function Manually to filter between two dates in google sheets

  • You will get all the available information from 5/20/2022 to 7/20/2022.

Read More: How to SUMIF Between Two Dates in Google Sheets (3 Ways)


2. Combining FILTER and DATE Functions

Alternative to applying the FILTER function manually is using the combination of two functions that are FILTER and DATE. Like previously, we want to filter data within dates 5/20/2022 to 7/20/2022.

Steps:

  • Select Cell B17, apply the following formula, and press Enter-
=FILTER(B5:F14,(B5:B14>DATE(2022,5,20))*(B5:B14<DATE(2022,7,20)))

  • And you will get all the available information within those two dates.

Formula Breakdown:

  • (B5:B14>DATE(2022,5,20))

Looks for the dates over “2022-5-20”.

  • (B5:B14<DATE(2022,7,20))

Then it looks for the dates before “2022-7-20”.

  • FILTER(B5:F14,(B5:B14>DATE(2022,5,20))*(B5:B14<DATE(2022,7,20)))

Filters entire rows that are within “2022-5-20” to “2022-7-20”.

Read More: Find Number of Months Between Two Dates in Google Sheets


Similar Readings


3. Employing QUERY Function

The output of the QUERY function is saved in a dynamic, temporary field created at query time and is tested against each result returned by the QUERY. This function works in the most efficient way for filtering but Must Remember, this function can read dates that are in “YYYY/MM/DD” format only. So, it is essential to set the criteria for this function as it should be. As we can see in the following dataset, the dates are in “MM/DD/YYYY”. So before applying this function, we need to set the criteria dates as in “YYYY/MM/DD” format. Suppose, we want to filter data from “5/20/2022” to “7/20/2022” in the following dataset.

Employing QUERY Function to filter between rows in google sheets

Steps:

  • In the following dataset, first select Cell I7, apply the following formula, and press Enter
=TEXT(I4,"yyyy-mm-dd")

  • What this function is doing here is converting the date format to the “YYYY/MM/DD” format.

  • Now, using the Fill Handle icon, drag down as shown to apply the same for Cell I5 to Cell I8.

  • We will simply type dates in “MM/DD/YYYY’ format in Cell I4 and in Cell I5, then it will be converted as “YYYY/MM/DD” format in Cell I7 and Cell I8.

  • Now, select Cell C16, apply the following formula and press Enter
=QUERY(B4:F14,"select * WHERE B >= date '"&I7&"' AND B <= date '"&I8&"'")

  • The QUERY function will filter data from the date “5/20/2022” to “8/10/2022”.

  • Now, assume you want to know all available information from “6/10/2022” to “8/25/2022”.
  • Simply type “6/10/2022” in Cell I4 and type “8/25/2022” Cell I5 and the result you will get will be as follows showing all the available data within “6/25/2022” to “8/25/2022”.

Read More: How to Calculate Time Between Dates in Google Sheets (6 Ways)


4. Using Custom Formula

We can filter data through customized formulas. Basically here we will use the Filter command with a customized formula. As before, presume, we want to filter all available data within the date “5/20/2022” to “8/10/2022”.

Steps:

  • Select Cell range B4:F14 and at the toolbar select Create a filter.

Using Custom formula to filter function in google sheets

  • The dataset will appear like this-

  • Now, select the dashed icon in Cell B4 beside “Order Date”, then select “Filter by condition”, then select “Custom formula is”.

Using Custom formula to filter function in google sheets

  • Now, apply the following formula in the empty box and press “OK”.
=(B4:B>=$D$16)*(B4:B<=$D$17)=1
Using Custom formula to filter function in google sheets

  • And all the available data will be filtered.

Read More: Calculate Number of Years Between Two Dates in Google Sheets


Conclusion

The FILTER function along with the QUERY formula is one of the most useful tools in Google Sheets. In this article, we have demonstrated 4 quick methods to FILTER between two dates in Google Sheets. It is kinda obvious that you use Google Sheets or Excel for storing your necessary data, information or whatever! So it is important for you to know the quickest method to get your necessary information from a larger dataset. Hope this article may help you with your tasks. Have a visit to our site officewheel.com for more relevant articles. Thank you.


Related Articles

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo