Finding the Closest Date to Today in Google Sheets

Published on

Knowing how to find the closest date to today in a list of dates is useful in many real-life scenarios in Google Sheets.

Finding the nearest date is useful for locating or identifying upcoming events, appointments, cash flow, health checkups, renewal dates, and more.

Similarly, finding the most recent past date is useful for identifying the most recently completed task, the last vehicle maintenance date, the last payment date, and so on.

To find the closest date to today, we can use many functions, but the easiest one is XLOOKUP.

Let’s see how to use XLOOKUP to find the closest date to today in Google Sheets. For the examples, we will assume the dates are in column A.

Finding the Most Recent Past Date in Google Sheets

You can use the following XLOOKUP formula to find the closest past date to today in Google Sheets:

=XLOOKUP(TODAY()-1, A1:A, A1:A, "", -1)

It follows the syntax XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Where:

  • search_key: TODAY()-1
  • lookup_range: A1:A
  • result_range: A1:A
  • missing_value: “”
  • match_mode: -1

In this formula, the search key is TODAY()-1. The formula searches for this key from the first value to the last value in the lookup range (A1:A) and returns the value that exactly matches or the next value that’s less than the search key from the result range (also A1:A).

The formula will return an empty string if there is no matching date.

This is the easiest way to find the most recent past date in Google Sheets.

Finding the Nearest Upcoming Date in Google Sheets

You need to make two changes in the above formula to get the nearest upcoming date in Google Sheets.

Replace the search_key TODAY()-1 with TODAY()+1 and match_mode -1 with 1.

Here is the formula to find the closest future date to today in Google Sheets:

=XLOOKUP(TODAY()+1, A1:A, A1:A, "", 1)

The formula searches TODAY()+1 in the lookup range, i.e., A1:A, for an exact match or the next value that is greater than the key. It will return the matching value from the result range, which is again A1:A.

The formula will return an empty string if there is no matching date.

Finding the Closest Date to Today in a Timestamp Column

The above formulas are specifically for a date column, not a timestamp column. However, if you use a sheet connected to a Google Forms form, you will likely have a timestamp column in your connected sheet.

In such scenarios, to find the closest date to today, you can use the following formulas:

Formula to Find the Most Recent Past Date in a Timestamp Column:

=XLOOKUP(NOW()-TIME(0, 0, 1), A1:A, A1:A, "", -1)

Formula to Find the Nearest Upcoming Date in a Timestamp Column:

=XLOOKUP(NOW()+TIME(0, 0, 1), A1:A, A1:A, "", 1)

The formulas are the same as the previous ones used for the date columns, except for the search keys.

Resources

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.

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

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

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

27 COMMENTS

  1. Hi,

    Is there a way to look up the next date to today from a list of dates in another workbook, please?

    Thank you.

  2. Thank you so much for your answer.

    I am using this formula to see my clients’ birthdays, and sometimes I have multiple clients that have their birthdays on the same day.

    How can I have the formula post all the results if there are multiple of the same date?

    Also, is there a way to filter this to see all upcoming birthdays within the next day or week?

    Thank you so much.

    • Hi, Jeffrey,

      Use FILTER to filter the table.

      For example, if the dates are in A2:A and client names in B2:B, you can use the below formula.

      =filter(A2:B,A2:A=TO_DATE(MAX(if(lt(A2:A,today()),A2:A))))

  3. Hi Prashanth,

    Thank you for posting this solution to finding the closest date as well as a value related to it.

    I had a few bumps when implementing that I was hoping you could help with.

    1. The formula returned an error when the date column wasn’t the first column.

    2. How would you handle the situation when multiple occurrences happen on the same upcoming date?

    Thanks!

    • Hi, Dom,

      Q1 (Answer):

      I understand that the date column is not the first column. Since Vlookup searches the first column, you require to re-structure the column virtually.

      In my Vlookup formula, the source range is A2:C8, where A2:A8 contain dates.

      If B2:B8 contain dates insted, you can re-arrange it as {B2:B8,A2:A8,C2:C8}

      So the formula will become

      =ArrayFormula(vlookup(TO_DATE(MIN(if(B2:B8>today(),B2:B8))),{B2:B8,A2:A8,C2:C8},3,0))

      Please check the details under the subtitle “Leftward Vlookup – Vlookup Advanced Tips # 4” in the Vlookup function guide.

      Q2 (Answer):

      Multiple occurrences in Vlookup is a different topic. You can read that here.

  4. Prashanth,

    Regarding my last message (not yet published) you can ignore, I found 1 of the cells in my date list had =TODAY() 😉

    • Hi, truly,

      I’ve read that comment.

      It seems the ‘working’ formula that you have shared in that, itself, was not correct.

      Please feel free to share the URL of your (sample) sheet in the below reply. I won’t publish.

  5. Hi, Prashanth,

    Do you have any idea why conditional formatting is not being applied to a column with dates in it generated by a REGEXEXTRACT formula?

    It works for a random date I entered in Column C, but not for dates in Column B.

    Formula:

    ={"Exp Date Only";arrayformula(if(A2:A="",,REGEXEXTRACT(A2:A,"[0-9]{2}/[0-9]{2}/[0-9]{4}")))}

    Thanks for your help!

    • Hi, Spencer,

      The output is text. Use the VALUE function around your Regex.

      E.g.:-

      ={"Exp Date Only";arrayformula(if(A2:A="",,value(REGEXEXTRACT(A2:A,"[0-9]{2}/[0-9]{2}/[0-9]{4}"))))}

      An alternative Regex formula.

      ={"Exp Date Only";arrayformula(if(A2:A="",,value(REGEXEXTRACT(A2:A,"[0-9/]+"))))}

      When you use this, you must format the output to Date from the Format menu.

  6. Hello, I would like to know how to adapt this formula to two VLOOKUPs instead of only the date.

    I want the date and another normal VLOOKUP criterion. Thanks.

    • Hi, Mina Boulos,

      There are many options. Here is one.

      Note:- The following examples are as per the table below the subtitle “Additional Tips: Closest Date to Today in Vlookup.”

      Past:

      =sortn(filter(A2:C,lt(A2:A,today()),B2:B="Driver 2"),1,0,1,0)

      Future:

      =sortn(filter(A2:C,gt(A2:A,today()),B2:B="Driver 2"),1,0,1,1)

      The above formulas will return all the columns. Wrap it with Query to select required columns.

      E.g.:-

      =query(sortn(filter(A2:C,lt(A2:A,today()),B2:B="Driver 2"),1,0,1,0),"Select Col2,Col3")

  7. Hi, is there a version of this that doesn’t use TODAY? But uses a specific date? eg: 12/1/20. I’ve tried multiple versions using a specific date, but it mostly returns 0.

  8. Thank you for the valuable tutorial!

    Is there’s a way to implement this logic to find the closest TIME to the current time?

    I have a list of dates (DATETIME) and some may fall on the same day but at different times.

    Please provide guidance if this’s possible!

    Thank you in advance!

  9. Thank you very much for the formula, I have an issue I hope you can tell me how to resolve it.

    If I use the formula to get the closest date in the future and the range only has past dates I get a result ‘Dec,30,1899’ what do I need to add so the result should be blank if there is no future date?

    Many thanks.

    • Hi, sruli s,

      Thanks for letting me know about the issue. Actually the said date “Dec,30,1899” is equal to 0. The To_Date in the formula converts 0 to “Dec,30,1899”.

      You can solve the issue and RETURN BLANK IF NO SATISFYING DATE by using the below formulas.

      I am using Maxifs and Minifs this time though the Max+If and Min+If will also work.

      Maxifs formula to find the past closest date to today():

      =if(maxifs(A2:A,A2:A,"<"&today())>0,to_date(maxifs(A2:A,A2:A,"<"&today())),"")

      Minifs formula to find the future closest date to today():

      =if(minifs(A2:A,A2:A,">"&today())>0,to_date(minifs(A2:A,A2:A,">"&today())),"")

      I hope these formulas help?

    • Hi, Matthew,

      Yup! Assume the dates are in columns A, B, and C.

      To find the past closest date, you can use the below formula.

      =ArrayFormula(TO_DATE(MAX(if(lt(A1:C,today()),A:C))))

      Here is a new formula using FLATTEN and FILTER (just for learning more)

      =to_date(sortn(filter(flatten(A1:C),lt(flatten(A1:C),today())),1,2,1,0))

      Similarly, to return the future closest date to today() in multiple columns, use either of the following two formulas.

      =ArrayFormula(TO_DATE(MIN(if(gt(A1:C,today()),A:C))))

      Here is the FLATTEN one.

      =to_date(sortn(filter(flatten(A1:C),gt(flatten(A1:C),today())),1,2,1,1))

      See if this helps?

  10. I need to Find the Past Closest Date to Today but for a specific landowner.

    That is I need to add a second IF statement inside the array_formula that pulls from column B where B = ‘land_owner_name’
    to: =ArrayFormula(TO_DATE(MAX(if(Com_Log!A:A<today(),Com_Log!A:A))))

    Can this be done?

    • Hi, Eran Chazan,

      Your question is how to find the past closest date to today() with a condition included.

      You may try to use the below formula. In that instead of IF + MAX, I’ve used MAXIFS as multiple max conditions are involved.

      =to_date(MAXIFS(A2:A,A2:A,"<"&today(),B2:B,"james"))

      Replace "james" with the landowner name.

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.