Finding the Closest Date to Today in Google Sheets

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

27 COMMENTS

Andy May 10, 2023 At 5:49 pm

Hi,

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

Thank you.

- Prashanth May 11, 2023 At 2:47 pm
  
  Hi, Andy,
  
  Import the data and use one of my above formulas.
  
  To import, use the IMPORTRANGE function.
  
Jeffrey January 5, 2023 At 1:15 am

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.

- Prashanth January 8, 2023 At 11:06 am
  
  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))))
  
CG September 9, 2022 At 10:58 pm

Can you help me: what would the formula be if I wanted to find the 2nd next future date among a set of dates?

- Prashanth September 10, 2022 At 9:50 am
  
  Hi, CG,
  
  This may help.
  
  =ArrayFormula(to_date(iferror(small(if(A2:A>today(),A2:A),2))))
  
Dom September 8, 2022 At 7:28 am

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!

- Prashanth September 10, 2022 At 9:42 am
  
  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.
  
sruli March 28, 2022 At 6:12 pm

Prashanth,

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

- Prashanth March 29, 2022 At 10:53 am
  
  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.
  
Spencer February 19, 2022 At 7:14 am

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!

- Prashanth February 20, 2022 At 3:42 pm
  
  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.
  
Mina Boulos January 19, 2022 At 3:03 am

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.

- Prashanth January 19, 2022 At 9:24 am
  
  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")
  
Alex January 29, 2021 At 5:11 am

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.

- Alex January 29, 2021 At 5:13 am
  
  NM, I immediately figured it out after I posted this. Subbed TODAY() for DATE(2020,12,01) and it worked.
  
- Heidi January 4, 2022 At 11:49 pm
  
  Were you able to ever figure out a way to have it lookup against a specific date that is a referenced date vs. a hard-coded date?
  
  - Prashanth January 5, 2022 At 11:40 am
    
    Hi, Heidi,
    
    Enter the hard-coded date using the DATE() function.
    
    Date to lookup: 31/12/2021
    Referenced date: D2
    Hard-coded date:=date(2021,12,31)
    
Abbe December 9, 2020 At 7:29 pm

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!

- Prashanth December 10, 2020 At 6:03 am
  
  Hi, Abbe,
  
  Replace today() in the formulas with now().
  
  If the result shows only date, format the result to timestamp from the menu Format > Number > Date time.
  
  - Abbe December 11, 2020 At 7:36 pm
    
    Thank you for your quick and helpful reply!
    Using now() did the trick.
    
sruli s September 9, 2020 At 10:02 pm

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.

- Prashanth September 10, 2020 At 9:44 am
  
  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?
  
Matthew August 19, 2020 At 5:28 pm

Hi, is there a way to adapt this formula for several columns of dates?

- Prashanth August 21, 2020 At 9:23 am
  
  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?
  
Eran Chazan June 19, 2020 At 11:22 am

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?

- Prashanth June 19, 2020 At 6:41 pm
  
  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.