Google Sheets FILTER function: Dates and Times

FILTER dates and times in Google Sheets Thumbnail

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.

Starter Sheet

Goat Club Rewards

You can get a copy of the starter sheet to play along and create your own reference Google Sheet here.

FILTER: dates & times in Google Sheets: STARTER

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:

Names and Dates raw data for Google Sheets FILTER example

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.

Hire me for our next Google Workspace project.

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.

Google Sheets FILTER by date equals cell date

⚠️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.

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

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))

Google Sheets FILTER by date equals in formula DATE v2

⚠️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"))

Google Sheets FILTER by date equals in formula DATEVALUE

⚠️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)

Google Sheets FILTER by date greater less not date cell

⚠️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") )

Google Sheets FILTER by date greatwe less not date within formula

⚠️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:

Google Sheets: How to use OR inside a 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)

Google Sheets FILTER between two dates with two cell referencesYou 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"))

Google Sheets FILTER between two dates with DATEVALUE in the formula

⚠️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):

Google Sheets FILTER date by weekend or weekdays

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.)

Google Sheets WEEKDAY

⚠️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.))

Google Sheets ARRAYFORMULA WEEKDAY⚠️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)

Google Sheets FILTER ARRAYFORMULA WEEKDAY

⚠️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)

Google Sheets FILTER ARRAYFORMULA WEEKDAY for weekends

⚠️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:

Google Sheets FILTER Time dataset

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)

Google Sheets FILTER Time equals a time in a cell⚠️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")

Google Sheets FILTER Time in formula string error

⚠️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’.

Google Sheets FILTER Time in formula infinite 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))

Google Sheets FILTER Time in formula with TIME function

⚠️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"))

Google Sheets FILTER Time in formula with TIMEVALUE function

⚠️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)

Google Sheets FILTER Time from cell reference greater or less than

⚠️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)

Google Sheets FILTER Time in formula greater or less than

⚠️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)

Google Sheets FILTER Time in formula between 2 times⚠️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”:

Google Sheets Date time stamp DTS sample data

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)

Google Sheets FILTER DTS by cell reference⚠️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")

Google Sheets Date time stamp DTS VALUE function

⚠️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"))

Google Sheets Date time stamp DTS VALUE function V2

⚠️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)

Google Sheets Date time stamp DTS greater or less than from cell

⚠️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"))

Google Sheets Date time stamp DTS greater or less than within formula

⚠️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.

Google Sheets Date time stamp DTS between two DTS cell referencesv2

 

=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"))

Google Sheets Date time stamp DTS between two DTS in formula⚠️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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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)

Google Sheets FILTER DTS after a certain time

 

 

 

Want to learn more about the Google Sheets FILTER function? Check out these tutorials:

~ Yagi

 

Leave a Reply