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?
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:
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].
