If you have landed on this page you may have discovered that dates and times don’t always work as you might expect with the Google Sheets FILTER function.
In this tutorial, we are going to cover how to use FILTER on dates, times and date and time stamps (DTS).
You can follow through the tutorial in its entirety or use the table of contents below to get directly to what you need.
Table of Contents
Starter Sheet
You can get a copy of the starter sheet to play along and create your own reference Google Sheet here.
Use the blue cells to enter your Google Sheet formula. You will be able to see the results and the formula appear after you have entered the results. There may be a time when we want to see an error formula so we understand the formula better, use the far-left cell of the red cells to see the result.
FILTER with dates
We will apply the FILTER function on the following data containing names and dates:
For simplicity, the range of data contains just a Name and Date column, but you could add other columns to this as well.
Note! I’m from Australia, so I use the Day/Month/Year format familiar with Commonwealth countries, but you could equally use the Month/Day/Year format found in the Philippines, Canada and the U.S.A.
P.S. My preferred format is the Year-Month-Day format. Much better for sorting data.
Dates from a Cell Reference
Filtering by date when you are referencing another date cell in Google Sheets is virtually the same as filtering by a number or text value.
Let’s filter down to display only ranges with the date “23/01/2024″.
=FILTER(A2:B94,B2:B94 = D4)
As a refresher for the FILTER function, the first parameter selects the range that you want to display and the following parameters are conditions containing True or False values returned from the condition that you set.
=FILTER(Display range,range to check =<> condition to meet)
In our example, we first get the full range of our data from A2 to B94. We then want to check column B against our date cell in D4.
⚠️Task: Add the formula into D6 of the ‘DATE’ sheet tab of the Starter Sheet. Change the date in cell, D4.
FILTER Dates Equal to a Date
FILTER Dates within the Google Sheets formula
Now, we will try and add a date condition directly into the formula without a cell reference. However, if we add a date like in these examples, we will get an error:
=FILTER(A2:B94,B2:B94 = "23/1/2034")
=FILTER(A2:B94,B2:B94 = 23/1/2034)
⚠️Task: In H6, try and use one of the formulas above and see what error you get.
What’s going on?
Dates are Just Floating Point Numbers in Google Sheets
How a date is displayed, is either generated intuitively by Google Sheets or manually when you set a cell or range using Format > Number > and selecting your own date format type. However, under the hood, dates are represented as floating point numbers (We might recognise this as a decimal number).
Each whole number accounts for one day since 1899 December 30. Each decimal number is the portion of a day’s hours, minutes and seconds.
We can check what a date, a time or a date and time stamp’s floating point number is by using the VALUE function.
⚠️Task: In E4, check out what the floating point number is for the date in D4 with the formula, =VALUE(D4). You should get 45314.
Using the ‘DATE’ function
We can use the DATE function in our FILTER condition to set the date within the formula. So to use the date ’23/01/2024′ as our condition, we would declare the DATE function and then apply the year, month and day all as integers for each of the three parameters.
=FILTER(A2:B94,B2:B94 = DATE(23, 01, 2024))
=FILTER(Display range, date range to check = DATE(YEAR, MONTH, DAY))
⚠️Task: In H10, add the formula above using DATE to insert the date for 23/01/2024. Hint, date takes 3 parameters.
Using the ‘DATEVALUE’ Function
Another alternative approach here is to use the DATEVALUE function. With this function, we can create a string of common date types, for example:
- “15/2/2024” // Flip the month and day around if that’s how you do it in your country.
- “2024/2/15” // Flip the month and day around if that’s how you do it in your country.
- “15-2-2024”
- “2024-02-15”
=DATEVALUE("Date as string")
=DATEVALUE("15-2-2024")
Let’s add this to the FILTER formula:
=FILTER(A2:B94,B2:B94 = DATEVALUE("23/01/2024"))
=FILTER(Display range, date range to check = DATEVALUE("Common date format as string"))
⚠️Task: In D6, add the formula above using DATE to insert the date for 23/01/2024. Hint, date takes 3 parameters.
FILTER Date Ranges Greater Than or Less than a DATE
Using a Reference Cell to Filter the Date Range
Here we can use the following symbols to display our range of data:
- Greater than the date:
>:=FILTER(A2:B94,B2:B94 > L4) - Less than the date:
<:=FILTER(A2:B94,B2:B94 < L4) - Greater than or equal to the date:
>=:=FILTER(A2:B94,B2:B94 >= L4) - Less than or equal to the date:
<=:=FILTER(A2:B94,B2:B94 <= L4) - Not equal to the date:
<>:=FILTER(A2:B94,B2:B94 <> L4)
⚠️Task: In L6, Add the less than formula to the cell. Note! If you try a different formula in the starter sheet you may get an overflow error because there is more data below.
Using DATEVALUE to filter a Range within the Formula
Again, we can use DATAVALUE within the FILTER along with the greater, less, equals or not expressions. Check out the examples:
- Greater than the date:
>:=FILTER(A2:B94,B2:B94 > DATEVALUE("23/01/2024")) - Less than the date:
<:=FILTER(A2:B94,B2:B94 < DATEVALUE("23/01/2024") ) - Greater than or equal to the date:
>=:=FILTER(A2:B94,B2:B94 >=DATEVALUE("23/01/2024") ) - Less than or equal to the date:
<=:=FILTER(A2:B94,B2:B94 <= DATEVALUE("23/01/2024") ) - Not equal to the date:
<>:=FILTER(A2:B94,B2:B94 <> DATEVALUE("23/01/2024") )
⚠️Task: In L26, Try out the formulas above to see how they filter the data.
You could also use the DATE function in the same way as the example above.
If you don’t want to include more than one date, then you will need to use an OR operator. Check out this tutorial on how to apply OR to FILTER:
How to FILTER between Two Dates
Filtering ranges between two dates is just a case of adding a new condition argument to your filter.
Filtering between two dates using two date cell references
Cell reference will require a start date cell and an end date cell. Let’s filter a range between 23/01/2024 and 02/02/2024.
=FILTER(A2:B94,B2:B94 >= P4, B2:B94 <= Q4)
=FILTER(Display range, date range to check >= condition 1, date range to check <= condition 2)
You can see here that we wanted to include the selected dates in our filter as well. If you don’t remove the equals(=) statement from the condition.
⚠️Task: In P6, Copy the filter above. Next, try the range to not also include the selected dates.
Using the DATEVALUE function to filter between two dates
Using DATEVALUE again we can do the same thing within the FILTER:
=FILTER(A2:B94,B2:B94 >= DATEVALUE("23/01/2024"), B2:B94 <= DATEVALUE("02/02/2024"))
=FILTER(Display range, date range to check >= DATEVALUE("date as string"), date range to check <= DATEVALUE("date as string"))
⚠️Task: After testing out the filter above in cell P39, play around with changing date ranges.
BONUS: FILTER Weekdays or Weekends only
We can tweak the FILTER function a little to exclusively display a range of only Weekdays or only Weekends.
We will need to build up our formula with two more functions.
Here is our starting data for this Chapter. Notice that it contains weekends (Marked in purple):
You can find this in the Starter Sheet tab ‘WEEKDAY DATE’.
STEP 1: The WEEKDAY Function
Let me introduce you to the Google Sheets WEEKDAY function.
This function takes a date as its first argument. The function then returns a corresponding number. By default Sunday is 1, Monday is 2 etc. all the way through to Saturday as 7.
We can, however, change the starting day with an optional second parameter. This will be handy for us for our formula calculations in a moment (Incidentally, here in Australia, we start our week on Monday, too).
=WEEKDAY(D3, 2)
=WEEKDAY(DATEVALUE("18/01/2024"), 2)
=WEEKDAY(Date, Optional: Start week on Monday.)
⚠️Task: On sheet’WEEKDAY DATE’, add the formula, =WEEKDAY(D3, 2), to cell D6. Then use the dropdown menu in D3 to seed the weekday number change.
Step 4: Get an array of weekday numbers
Next, we can use the ARRAYFORMULA function to execute WEEKDAY over our range of dates.
=ARRAYFORMULA(WEEKDAY(B2:B94, 2))
=ARRAYFORMULA(WEEKDAY(Date range, Optional: Start week on Monday.))
⚠️Task: Now in cell D11, add the formula above to see the weekday number value for each day in the range.
step 5: Filter to display weekdays
Now, let’s add this formula to our FILTER function.
We will only want to display Monday through to Friday (1-5). We can do this by making our WEEKDAY array condition less than 6.
=FILTER(A2:B94,ARRAYFORMULA(WEEKDAY(B2:B94,2)) < 6)
=FILTER(Range to dispaly,ARRAYFORMULA(WEEKDAY(date range,Optional: Start week on Monday)) < 6)
⚠️Task: Go ahead and plug this in H6 to see only the weekdays.
Step 5: FILTER to Display Weekends
Alternatively, we can make our condition greater than 5 to display only weekends:
=FILTER(A2:B94,ARRAYFORMULA(WEEKDAY(B2:B94,2)) > 5)
=FILTER(Range to dispaly,ARRAYFORMULA(WEEKDAY(date range,Optional: Start week on Monday)) > 5)
⚠️Task: Go ahead and plug this in L6 to see only the weekend.
FILTER Times in Google Sheets
Time to work on erhm… time with FILTER now.
Here is what the dataset looks like:
Filtering time from a CELL reference (time equals)
Here we can safely reference a time in a target cell with FILTER:
=FILTER(A2:B94, B2:B94 = D4)
=FILTER(Range to display, time range to check = condition to meet)
⚠️Task: On the ‘TIME’ sheet tab, add the formula to cell, D4. You should see two results.
Filtering times within the FILTER function
Just like with dates, we cannot simply reference a string as a time like "07:12:06". So this will create an error:
=FILTER(A2:B94,B2:B94 = "07:12:06")
⚠️Task: Give this a try in cell H6. What error does it generate?
A Strange ERROR
Perhaps the next possible solution is to use the TIME function for our condition.
=FILTER(A2:B94,B2:B94 = TIME(7,12,6))
However, this is also going to generate a more counterintuitive error: a ‘No match error’.
⚠️Task: Add the formula to H8. Why is there no match when we can see a time in our range of data?
What’s going on?
Note that this may not occur all of the time with your data and may just be a result of data that you have copied and pasted in.
This seems to be due to slight differences in how floating point numbers are rounded in Google Sheets.
Our time values are stored in Google Sheets as decimals so:
07:12:06 = 0.3000694444
However, there may be slight changes to the decimal point values especially with long or infinite decimal places. This results in the ‘N/A’ error.
Converting each time in the range to a VALUE
Fortunately, we can solve this by using the VALUE function on each time in the range to ensure that it accurately matches the value we produce with time.
To apply this to all values in the time range, we can use the ARRAYFORMULA function.
More examples of the ARRAYFORMULA function in use here.
Let’s add this to our FILTER function.
Using FILTER to Equal a Time within the formula using TIME
After selecting our range, we first convert our time column to values. Then we apply our condition using the TIME function to those values.
=FILTER(A2:B94,ARRAYFORMULA(VALUE(B2:B94)) = TIME(7,12,6))
⚠️Task: Try just the array formula in cell H21 to see how the ARRAYFOMULA works and then replace it with the formula above.
Filtering a specific time within a formula with TIMEVALUE
Just like DATEVALUE, time has a TIMEVALUE function that allows you to use a standard time format as a string and convert it to a time. For example:
TIMEVALUE("7:12 AM")TIMEVALUE("07:12:06")TIMEVALUE("07:12:06.11 AM")
Now, let’s add this to our formula:
=FILTER(A2:B94,ARRAYFORMULA(VALUE(B2:B94)) = TIMEVALUE("07:12:06"))
⚠️Task: Add the formula to H21
Google Sheets FILTER Time: Greater Than or Less Than a Time
Referencing a cell value
To get the time greater than or less than a time from a reference cell we can do the following:
- Greater than the time:
>:=FILTER(A2:B94,B2:B94 > L4) - Less than the time:
<:=FILTER(A2:B94,B2:B94 < L4) - Greater than or equal to the time:
>=:=FILTER(A2:B94,B2:B94 >= L4) - Less than or equal to the time:
<=:=FILTER(A2:B94,B2:B94 <= L4) - Not equal to the time:
<>:=FILTER(A2:B94,B2:B94 <> L4)
Currently, the source data is a little jumbled up. Let’s include the SORT function to sort our range by the second column. The SORT function takes at least 3 arguments:
- Range to sort
- Column number to sort by
- True for ascending order or false for descending order
- More sort columns.
For us, we want to sort by our time column (column 2) in ascending order.
=SORT(FILTER(A2:B94,B2:B94 <> L4), 2, TRUE)
⚠️Task: Insert the formula above into N6.
Within the formula
We will use the TIMEVALUE function to incorporate the condition into the FORMULA.
- Greater than the time:
>:=SORT(FILTER(A2:B94,B2:B94 > TIMEVALUE("07:12:06")),2,TRUE) - Less than the time:
<:=SORT(FILTER(A2:B94,B2:B94 < TIMEVALUE("07:12:06")),2,TRUE) - Greater than or equal to the time:
>=:=SORT(FILTER(A2:B94,B2:B94 >= TIMEVALUE("07:12:06")),2,TRUE) - Less than or equal to the time:
<=:=SORT(FILTER(A2:B94,B2:B94 <= TIMEVALUE("07:12:06")),2,TRUE) - Not equal to the time:
<>:=SORT(FILTER(A2:B94,B2:B94 <> TIMEVALUE("07:12:06")),2,TRUE)
⚠️Task: Insert the formula above into N36. Try to change the operators around (<,>,<=,>=, <>) to see what result you get. The change the date.
FILTER the Time Between Two Periods in Google Sheets
Finally, let’s use the FILTER function to collect times between two dates.
Let’s try it with the TIMEVALUE function:
=SORT(FILTER(A2:B94, B2:B94 >= TIMEVALUE("07:00:00"),B2:B94 <= TIMEVALUE("08:00:00")),2,TRUE)
⚠️Task: Add the formula above into R6. Try and change the start and end values. You could even try and convert the formula to run from cell references.
Google Sheets FILTER with DATE-TIME stamps
In this section, we will use the FILTER function on Date-Time stamps (DTS or timestamps).
As a bonus, we will also cover how to filter a DTS by just time.
Here’s the sample data from the Starter Sheet in tab “DTS”:
FILTER by exact DTS
Using a cell reference
Just like pure dates and times, we can simply refer to a target cell as our filter condition:
=FILTER(A2:B94,B2:B94 = D4)
⚠️Task: Update the formula into the DTS sheet tab at cell D6.
FILTER Date and Time in Google Sheets Inside Formula
Just like dates and times, the FILTER function will not be able to take a DTS string as a valid argument. So this will fail:
=FILTER(A2:B94,B2:B94 = "18/01/2024 07:52:16")
⚠️Task: Add the erroneous function to H6 of your Starter Sheet for reference.
Here we can use the VALUE function on a date string within the function.
=FILTER(A2:B94,B2:B94 = VALUE("18/01/2024 07:52:16"))
⚠️Task: In cell, H12, add the formula above.
FILTER Date and Time before or after a DTS
Using a Cell Reference
We can filter using a cell reference to display ranges greater than or equal to a value (<,>,<=,>=, <>). Let’s filter all dates less than “19/01/2024 07:00:00”.
=FILTER(A2:B94,B2:B94 < L4)
⚠️Task: In cell, L6, insert the formula. Try an earlier date and make it less than or equal to.
Within the Formula
The VALUE function can be used again to apply the DTS condition within the formula:
=FILTER(A2:B94,B2:B94 < VALUE("19/01/2024 07:00:00"))
⚠️Task: In cell, L18, insert the formula. Play around with greater than and less than and change some of the dates.
FILTER DTS between two Date-Times
Using Cell references
Two cell references can be used as conditions on the came DTS column to return a range of date-times between two date-time values.
=FILTER(A2:B94,B2:B94 >= P4,B2:B94 <= Q4)
⚠️Task: Update P6 with the formula above.
Within the Formula
Next, we can apply the VALUE function to two date-time strings to set a condition range when running the filter without a cell reference.
=FILTER(A2:B94,B2:B94 >= VALUE("19/01/2024 07:00:00"),B2:B94 <= VALUE("25/01/2024 07:00:00"))
⚠️Task: Update 31 with the formula above.
FILTER DTS by Time
There might be a situation where you would like to filter your date-time range by range. Let’s say that you want to filter your date-time stamps to only show those after 9:00 AM.
First, we must convert our DTS column in our condition argument to time values with the TIMEVALUE function.
We can then use ARRAYFORMULA to iterate overall DTS in column B converting them to time values.
Now, we can compare these time values against our own time.
Finally, we will need to sort these time values because they could be anywhere in our range.
Here’s the formula:
=SORT(FILTER(A2:B94, ARRAYFORMULA(TIMEVALUE(B2:B94)) >= TIMEVALUE("09:00:00")),2, TRUE)
Want to learn more about the Google Sheets FILTER function? Check out these tutorials:
- Filtering IMPORTRANGE data in Google Sheets
- Google Sheets: How to use OR inside a FILTER
- Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.
- How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.
- How to get the most frequently appearing words in a column in Google Sheets
~ Yagi