Adrey
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.
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”.
- 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”.
- Type “ 5/20/2022” in the first empty box and then “7/20/2022” in the second. Finally, select “OK”.
- 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
- Generate Random Numbers or Text Between Limits in Google Sheets
- How to Find Correlation Between Two Columns in Google Sheets
- Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)
- Google Sheets Count Cells Between Two Numbers with COUNTIF Function
- How to Use IF Condition Between Two Numbers in Google Sheets
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.
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.
- 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”.
- Now, apply the following formula in the empty box and press “OK”.
=(B4:B>=$D$16)*(B4:B<=$D$17)=1
- 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
- Calculate Hours Between Two Times in Google Sheets
- How to Find Missing Values Between Two Columns in Google Sheets
- Conditional Formatting Between Two Values in Google Sheets
- How to Find Unique Values Between 2 Columns in Google Sheets
- Calculate Percentage Difference Between Two Numbers in Google Sheets
- Use REGEXEXTRACT Function Between Two Characters in Google Sheets
- How to Insert Lines Between Cells in Google Sheets
