Query to Filter Closest Higher Date to Today in Google Sheets

Published on

Another tough time for me to come up with a proper title. Hope you could understand the term closest higher date to Today in Google Sheets. If not here is what I meant.

I have the following data with me in the range A2: C12. Today is 4th of December 2018. Then as per the sample data below, row # 7 contains the closest higher date to today.

How to extract or filter such n number of closest higher date to today in Google Sheets?

filter nearest date to today using Query

Extract Closest Higher Date to Today in Google Sheets

I am going to use Query to filter n number of rows that are the closest higher date to today() in Google Sheets.

I am using Query because we want to sort the data based on the date column, filter it based on today() function and return n number of rows.

All that’s possible with a single Google Sheets Query and different clauses in it.

Clauses in Use:

WHERE – to filter the data based on today() date.

ORDER BY – Sort the Data Ascending Order.

LIMIT: To return n number of rows.

The Formula to Filter Closest Higher Date to Today in Google Sheets:

=query({A2:C},"Select * where Col1>date '"&text(today(),"yyyy-mm-dd")&"' order by Col1 Asc Limit 1")

Formula Explanation and Output:

Filter Closest Higher Date to Today in Google Sheets

I am splitting the above formula as follows for explanation purpose:

Formula 1: Where Clause.

=query({A2:C},"Select * where Col1>date '"&text(today(),"yyyy-mm-dd")&"'")

Output:

5-Dec-2018 Alfonso 10:00 AM
5-Dec-2018 Minnie 4:00 PM
7-Dec-2018 Karen 9:00 AM
Sat, 8 Dec 18 Terence 2:00 PM
9-Dec-2018 Alison 2:00 PM

Formula 2: Where Clause + Order By Clause

=query({A2:C},"Select * where Col1>date '"&text(today(),"yyyy-mm-dd")&"' order by Col1")

Output: The output will be the same. Here only sorting in ascending order is involved as an additional clause. My source data is already sorted. So the sorting won’t have any effect here.

Formula 3: Where + Order By + Limit

This is my final formula that includes the Limit clause. It just limits the number of rows to 1.

The logic is like this. The formula # 1 filters the date wherever the date in column 1 is greater than today().

The formula # 2 sorts the data in ascending order (based on column 1). So the closest higher date to today will be on the top. The limit clause in the final formula just limits the row # 1.

Now you may want to learn the use of today() in Query. So I recommend you to check my date criteria usage guide in Query.

Must Check: How to Use Date Criteria in Query Function in Google Sheets.

This way you can filter the nearest date to today() in Google Doc Sheets.

Related Reading:

1. Query to Filter Previous N Dates in Google Sheets.

2. Find the Past or Future Closest Date to Today in Google Sheets.

3. How to Lookup Latest Dates in Google Sheets [Array Formula].

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.